Data Tier Applications

  • Article

I have a confession to make; I have been doing a little bit of development! I wanted to check out one of the new features in Visual Studio 2010 / SQL Server 2008 R2, data tier application projects (confusingly shortened to DAC).

Essentially this is a simple lift and shift capability that allows a developer to deploy a database to production after which the DBA can manage it.

In VS2010 I can create a SQL Server Data Tier application ..

image

into which I can add database objects such as tables, views, stored procedures etc. from scratch.  If I have a database already that I want to start developing then I can simply create a new project and then right click on it to import a data tier application (only from SQL Server 2008 R2 I’m afraid).

image 

Now it’s in VS2010 I can compare it another version of the schema add objects, deploy it to another server e.g. the local copy on your machine complete with all the security logins etc.

You can also create a DAC from SQL Server Management Studio (SSMS) by selecting the database you want then Tasks –> Extract Data Tier Application to bring up this wizard. This creates a file of all of your settings into a file with a DACPAC extension which is actually a zip file full of xml files describing what you have extracted (which you can prove by changing the extension to zip & opening it).

BTW when I did this against a sample copy of AdventureWorks I got an error that ddl triggers aren’t supported in DAC, so watch for that.

The other interesting thing about DAC is that when it is deployed as such to a server it can also be monitored using the new Control Point feature in SQL Server 2008 R2, which I’ll leave for next time.

If you want to try any of this then there is the updated Developers Training Kit for SQL Server 2008 R2 which has examples and videos  of this and a lot of the other new stuff for R2 (and the older one for SQL Server 2008 here).

OK enough of development back to the exciting world of infrastructure!

Technorati Tags: sql server 2008 R2,VisualStudio 2010,data tier application,DAC