What You Need To Know About Data-tier Applications (DAC Fx)
The Data-tier Application Framework (DAC Fx) was introduced in SQL Server 2008 R2 as part of an improvement category known as “Application and Multi-Server Management” (AMM). It’s a completely new way to manage the handoff of database schemas from development to production, and in my view is probably the most significant advancement in the developer stack for SQL Server in quite some time.
DAC Fx was the key enabling technology for AMM, but unfortunately it got lost in the noise surrounding the broader improvement category, so very few SQL Server developers know anything about it. The same thing is happening again with the announcements surrounding Simplified Import and Export of Data for SQL Azure. You might think that DAC Fx is some kind of import/export tool, which would be wrong. Let me set the record straight.
So what is DAC Fx? It’s a set of tools, API’s and services designed to dramatically simplify the development, deployment and management of SQL Server database schemas. The best way to understand DAC Fx is to compare it to how most developers do things today.
Most developers build Transact-SQL scripts that create all the objects in a database schema (tables, views, stored procedures, etc.). When they are ready to deploy, they hand the scripts off to a DBA who is responsible for provisioning the production database. So far so good. The problem comes in when the developer wants to ship a new version of the database schema after the database has been in production for a while. Most developers maintain two sets of scripts, one to create the database from scratch, and a second to update the database from the previous version to the next version. These scripts can get very complex and have to be tested to guard against data loss or regressions. And those are the NICE developers. Often, all DBA’s get is one set of scripts to create a new empty database, and they have to create their own hand-reconciled migration scripts from scratch. This is extremely time consuming and error prone, and we consistently hear feedback that we should offer a better solution.
DAC Fx IS that better solution. Instead of building a bunch of Transact-SQL scripts to deploy a database, Visual Studio 2010 developers build a “.dacpac” instead. If you crack open a dacpac, you find a bunch of XML schema that describes the objects in the database that was just built. The dacpac can then handed off for deployment.
There are a bunch of ways to deploy a dacpac but let’s focus on how a DBA might do it. SQL Server 2008 R2 Management Studio has a new “Data-tier Applications” node in the object explorer (stop ignoring it!). All a DBA has to do is right-click that node and select the “Deploy Data-tier Application” command which launches a wizard that allows the DBA to specify a dacpac to deploy. Now DAC Fx takes over and cracks open the dacpac, parses all that good metadata, and hydrates a database that matches the schema in the dacpac.
At this point you may be yawning asking yourself why this is any better than just running scripts. Well here’s why. At some point that developer is going to change the database schema. All they need to do is build a new dacpac, and using the same approach the DBA can upgrade a Data-tier application to a new release. DAC Fx does all the heavy lifting to make this happen, no more hand-reconciled migration scripts.
It’s a great story and I’m very stoked about it, but before you get too excited you need to know that not all SQL Server features are supported in DAC Fx 1.0 which was was missing key stuff like spatial types, which are now supported in 2.0. You also should know that 1.0 uses a side-by-side approach when upgrading Data-tier applications, which means it creates a new empty database, and copies over all the data, then swaps the names. The old one is left hanging around just in case. As of Visual Studio 2010 SP1, DAC Fx supports in-place upgrades as well.
Another cool thing that will be supported in DAC Fx 2.0 is support for more than just schema, but for data as well, hence the import/export misnomer. Now you can include JSON serialized dumps of table data right along side your schema in a new file format called “.bacpac” that guarantees fidelity with the native SQL Server data types. Using DAC Fx, you can extract a bacpac from an existing database, and deploy it to a different SQL Server environment, including SQL Azure.
This solves a key problem for a lot of SQL Azure developers by giving them an easy way to move both the schema AND the data for a particular database to and from SQL Azure. So now that you understand the full power and capabilities of DAC Fx, start thinking about how to jettison all those pesky database deployment routines and migration scripts and start using DAC Fx to handle all your database deployment and upgrade needs. This will be possible for most simple SQL Server or SQL Azure database applications, and is becoming increasingly feasible for even more complicated enterprise-class solutions.
You can get started learning about DAC Fx 1.0 now with the SQL Server 2008 R2 Update for Developers Training Kit, just install the kit, open default.htm, and navigate to the SQL Server 2008 R2 > Application and Multi-Server Management page. Once you get your sea legs, check out the new DAC Fx 2.0 CTP Feature Pack which adds support for both schema and data.
Comments
Anonymous
May 01, 2011
Is this applicable to those of us that have to deploy updates to databases that are part of applications we sell, where we build our own installer to run scripts generated by VS 2010 Schema Comparisons? In other words, is DAC Fx accessible via an API or only through SSMS?Anonymous
May 05, 2011
DAC provides all of its services through public interfaces to enable scenarios just like you describe, Mark. Take a look at the documentation starting here: msdn.microsoft.com/.../microsoft.sqlserver.management.dac.dacstore.aspx And at the PowerShell samples available here: blogs.msdn.com/.../dac-powershell-samples.aspx To see how the interfaces are used. You can install just the minimal set of binaries by running these MSIs: www.microsoft.com/.../details.aspx That way you avoid having to install SSMS/SQL.Anonymous
June 07, 2011
Mark sorry it took me so long to respond. Yes, we do see commercial applications as one of the primary targets for DAC Fx, both for initial deployments and for upgrades. Where today you distribute your databse inatall / upgrade code as scripts, possibly embedded in an MSI as encrypted resources, tomorrow you will bundle your .dacpac or .bacpac in your MSI. And yes, DAC Fx is fully programmable, the current API documentation is here: msdn.microsoft.com/.../microsoft.sqlserver.management.dac.aspxAnonymous
November 21, 2013
So is this now possible in the latest DAC Fx? Can you suggest any good articles, tutorials, samples on how to do this? Since I am using Visual Studio which no longer comes with an installation tool, I am using WiX to create my MSI. Would love to see how to incorporate dacpac deployments into an MSI using WiX. Is that asking too much? :)