SQL Server 2008 R2 DAC

.dacpac LOL. Gotta love that extension!

Playing around with SQL Server 2008 R2 CTP (August), I really like the new data-tier application components feature.  If you haven’t read about this, it’s a simple abstraction of a given SQL Server database as it relates to an application in XML form, and this abstraction bundles all of the objects and ties them to a specific application.  This handy feature creates a bridge between the database and the app, which has been needed for a long time in my opinion.  For a DBA, it makes moving all of the components, including logins, connection string, etc. a snap, and enhances organization.  For an app dev,  it creates a nice deployment package to go along with other deployment utilities.  So, when you decide to move to production from development, or wish to create a replica of a database on another server, creating these components allows you to bring along all of the objects needed for a particular application.  No more, “Hey you idiot, you forgot to include proc xxx and now the app doesn’t work!” 

To build one, create a new DAC by doing a Tasks >> Extract Data-Tier Application move in Management Studio.  After you create the .dacpac file (which is really, if you change the extension, a .zip file with four files), do a Deploy Data-tier Application on your target server.  Very nice!

A couple of caveats that I ran into when trying this out. First, I read somewhere – I think on social.msdn.com, that all of the objects in SQL Sever are currently not supported for this method right now. So if you become enterprise-minded and want to convert AdventureWorks, it won’t work.  I also tried to move a database that had CLR functions and got an error.  Another catch,  I didn’t like how the tool worked when you created a newer version and then attempted a deploy to an existing database (see graphic, below). Hopefully the feature will work on existing databases – I’d hate to drop and recreate a 200GB database when I want to move only a few tables, users, or stored procedures.  And finally, how do you use it with multiple databases for a given application?  I didn’t see this, but it will certainly be needed.

Lee Everest

------------------------

Keep your .dacpac away from me, buddy