Offical team blog for SSDT, a tool for on and off-premise database development
I’m pleased to announce the latest release of SSDT Power Tools! We continue to use power tools to get early versions of experiences or quick features to you and we’re always interested in hearing your feedback. This release of the tools (Version 1.3) builds on the previous release.
This release of the power tools is only compatible with the newest update for SQL Server Data Tools. First, get the SSDT – September 2012 update for Visual Studio 2010 or Visual Studio 2012 here:
SSDT for Visual Studio 2012: http://msdn.microsoft.com/en-us/jj650015
SSDT for Visual Studio 2010: http://msdn.microsoft.com/en-us/jj650014
For the first time, this power tools release provides a version of the power tools for Visual Studio 2012 in addition to the version for Visual Studio 2010. They are separate installs that you can grab here: Edit: SSDT Power Tools are no longer available via the links below, but have been integrated into the core SSDT product in the December 2012 release (http://blogs.msdn.com/b/ssdt/archive/2012/12/13/available-today-ssdt-december-2012.aspx)
SSDT Power Tools for Visual Studio 2010
SSDT Power Tools for Visual Studio 2012
In this version, we added commands in SQL Server Object Explorer to create and deploy *.dacpacs (Data-tier Applications), the core artifact of the DAC Framework. You can find these commands by right-clicking on the Databases node or individual database nodes in SSOX. These commands also provide the ability to include data for a select set of tables in .dacpacs. Read on for details!
What are these things?
First, a few concepts to review, both old and new.
Data-tier Application/*.dacpac: A .dacpac Data-tier Application is a single file representation of a database. It contains the full schema of the database and optionally, selective sets of data. We frequently refer to .dacpacs in context as packages, and sometimes by DAC in general.
Data in .dacpacs: You can now include data from a specified set of tables in .dacpacs when created from a database (via Extract). Today, the tables selected for data to be included in the .dacpac must be in a closed reference set; that is to say, no foreign key references can extend outside of the set of tables selected. When you deploy (i.e. Publish) a package, data will be deployed automatically if it is present. The data deployment method used today is what we call Rip and Replace. If there is data in the package for TableFoo and TableFoo already exists in the target database, existing data in TableFoo will be deleted and the data defined in the .dacpac for TableFoo will be inserted. To be clear, existing data in other tables, for which there is no data set included in the package, will not be touched by the data deployment step.
Including data in the creation and deployment of .dacpac’s is something we are beginning to introduce but does not yet have the robust support that our schema deployment does – but we’ll get there! For example, we do not yet provide many options for controlling the deployment of data so please do use caution with data in .dacpacs, since it will replace data. As always, we are eager for feedback since this is a developing feature, for both the data deployment part of the DAC Framework and as we begin to surface it in SSDT.
Registered Data-tier Applications: Registering a database as a Data-tier Application is a technique for version management and drift protection. Registering stores a schema-only .dacpac on the server built from the database at that point in time. When a database is registered, subsequent schema deployments to that database go through a drift detection step first: if the live database is no longer the same as the registered .dacpac version stored on server, deployment will (optionally) be blocked because the schema may no longer be as you expect. Depending on your team development strategies, this may be useful to prevent unexpected results from a deployment or to prevent reversing team member changes prematurely.
DAC Properties: Metadata for the Data-tier Application (.dacpac). The metadata includes Application Name, Version, and Description and can be used to keep track of your packages in the Register process. The properties can be edited during Register and Extract.
What can I do?
Now we get into the context menu commands in SSOX.
Extract: Creates a .dacpac from a live database; same behavior as Extract in the DACFx API and SqlPackage.exe command-line utility.
This does not make any changes to the live database itself or any registered .dacpac on the server. You can set DAC Properties for the new package that is the output of this command. By default, this extracts schema only, but you can select tables for which you want data included as well. Remember that there can’t be any foreign key constraints involved with tables outside the set you choose to include, and that if this package is deployed, existing data in the tables you chose during Extract will be replaced with the data from the package.
Publish: Deploys a .dacpac to create or upgrade a database; same behavior as Publish in SQLPackage.exe and Deploy in the DACFx API.
Just like publishing a project from SSDT, if the database does not exist, it will be created, and if it does exist, it will be updated. Note that Publish does not have options to exclude or include data; if data exists in the .dacpac package, it will be deployed.
Register: Registers an existing database as a Data-tier Application.
This builds a schema-only .dacpac from the live database and stores it on the server, along with the DAC Properties you set.
Unregister: Unregisters a database as a Data-tier Application.
This deletes the .dacpac stored on server, its DAC Properties, and version history, but does not affect the live database in any other way. The database is no longer associated with a point-in-time DAC version.
Give the new SSDT Power Tools a try!
Edit: SSDT Power Tools are no longer available via the links below, but have been integrated into the core SSDT product in the December 2012 release (http://blogs.msdn.com/b/ssdt/archive/2012/12/13/available-today-ssdt-december-2012.aspx)
For Visual Studio 2010
For Visual Studio 2012
Remember, Snapshotting and Building SSDT database projects produce .dacpac’s, so you can use these commands to Publish those snapshots! You can also use this to start playing around with including seed data in .dacpacs, which will eventually evolve as we move forward into reference data and data comparison scenarios.
Data-tier Application/*.dacpac is a great feature, but when you select tables they are not sorted alphabetically. That is quite inconvenient.
I can't uninstall SSDT from VS2010. When I try to build my solution, VS2010 says it cannot create an App Domain.
I have a quick question re the new Sept release. It's my understanding that there is NOT a command-line way to generate the publish.sql script (i.e., script that is generated in the UI's 'generate script' button). Is that correct?? I've scoured sqlpackage documentation and haven't found a hit. Thanks for reading. BTW I love SSDT. Currently trying to get it adopted in my shop.
@Cyclon2005 Uninstall instructions are detailed here: msdn.microsoft.com/.../jj542427(v=vs.103).aspx
@msbigot Thanks for the positive feedback. sqlpackage's Script action handles the scenario you've described. Let me know if you have any questions about how to invoke it.
I don't know if this is the appropriate place, but I've noticed a curious thing about Synonyms. I can't get my synonym bound to the right database (even when I use the $(DatabaseName). notation in the CREATE). Oh, I should mention this is via MSBuild and SqlPackage. Is this behavior, by design?? The database name I get is the one that I imported to create the sql project. Thanks.
If it would'a been a snake, it would'a bit me.
Don't know if my problem is related with this update but after applying this update my localdb structure in SSOX got completely ruined!!
Let me try to explain myself: I work with SQL projects in VS2010 and TFS 2010, I have the same database projects branched for each of my clients, each client has mostly 3 branches each: Main, Dev and Release. Each solution/branch has 10 databases projects in them. The problem is that if I compile all the branches in the same machine the (localdb)\projects structure in SSOX shows something like this:
And so on. So I end up with about 30 databases for each of my clients not knowing which one corresponds to what branch:
Also there used to be a folder inside each of my SQL projects called Sanbox, now is no longer generated, and all the files are now stored in
C:\Users\myuser\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances
I liked the old way of store because I downloaded my projects lets say to a D drive with more space in it, now everything is stored in C and again I don´t know which file corresponds to what project :(
Is there a way to consider this TFS scenario and branching model to group in SSOX the Databases depending in the TFS branch name? It doesn't work naming after the sln name because is the same in every branch.
I'd suggest altering your project (via the Debug property page) to point to a different database for each branch. Ideally don't do this in the project file because you'll get merge conflicts. Instead I'd create something to distinguish one branch from another and use that in a .targets file you import into your .sqlproj.
For instance, if you have an environmental variable called "Branch2" then use that to alter your TargetDatabase:
Contents of Branch.Targets:
<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" xmlns="schemas.microsoft.com/.../2003" ToolsVersion="4.0">
Now place the import of Branch.Targets after the import of our targets file in the .sqlproj.
<Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
It would be helpful to tag this post with the 'SSDT Power Tools' tag.