New SSDT Power Tools! Now for both Visual Studio 2010 and Visual Studio 2012
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: https://msdn.microsoft.com/en-us/jj650015
SSDT for Visual Studio 2010: https://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 (https://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
What’s new?
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 (https://blogs.msdn.com/b/ssdt/archive/2012/12/13/available-today-ssdt-december-2012.aspx)
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.