Apr 25 2011

SQL Azure Deployments

You’ve set up your access to a SQL Azure site so you’re ready to go. Now how do you get your database up there? Well, TSQL works just fine, so you can script it all out to a file or set of files and apply them. Because you can connect with SSMS, you could use the GUI to build out your database. You can also use the Azure development platform and it’s web based GUI to create your database structures. Personally, the scripting method doesn’t seem too bad to me because you can get your code into source control that way. But, Microsoft has something additional in mind. It’s called Data-tier Applications or DAC for short (and yes, there are other things called DAC in SQL Server, what can I say, I don’t work for Microsoft).

A DAC is created by a DAC package, called a DAC pac (yes, yes, I know). A DAC pac can be created one of two ways. You can use Visual Studio 2010 to create a data-tier application, similar to how you would create a regular database in Visual Studio using the Team System database tools. The other option is to create a database using TSQL and/or SSMS and then extract a DAC definition. For this blog post, we’ll just talk about the method of extracting a DAC and creating a DAC pac (and yes, I’m having fun saying this over & over. Can you see a SQL Server MVP near by? His blood pressure is spiking every time he reads those letters).

I have a sample database I’ve been using for some different tests, it consists of a set of tables and primary & foreign keys. I sometimes load it up with data and other times leave it blank. Anyway, here is the list of tables in the database:

MovieManagementDB

To turn this into a DAC package is pretty easy. I’m simply going to right click on the database itself, click on the “Tasks” menu choice and then select “Extract Data Tier Application” This opens a wizard:

Extract_Step1

It’s pretty simple from there. Click on the “Next” button to bring up a few options that you can set. Not much really. You can name the package, give it a version, include a description and give it a location to store the package.

Extract_Step2

Clicking next validates that what you’re making into a DAC pac is a valid set of structures:

Extract_Step3

And then the final click of the Next button builds out the package and you’re done.

Extract_Step4

You would use this approach because it does a couple of things. First, it lets you develop stuff locally. You’re working on your own server using tools you’re comfortable with. Second, you can pass this to another team or another company and they can deploy your database from this package. The concept is, this is all you ever need to deploy a new structure to the database. It’s supposed to do upgrades and everything, all on it’s own. I’m working on testing it.

To deploy this to an Azure database, after you connect to the server in SSMS, open the Management folder. The only thing in it for a SQL Azure server is Data Tier applications. Right click and select “Deploy Data-tier application…” This will open another wizard and you can start by selecting the DAC pac you just created:

Deploy_Step1

You don’t really need to see all the screens because it’s just the other ones in reverse except you get to decide the name of the database you’re deploying. That’s it.

A couple of issues I have with this so far… no data. Microsoft is working on some other utilities for getting data out to the database, and I’ll cover those. No source control. Yeah, you can take the DAC pac itself, it’s just XML, and put that into source control, but that’s not the same thing as having individual tables, indexes and procs in source control as you can do using other tools.

Let me test these some more and I’ll post a few more bits of information about what I find.

8 Comments

  • By Andrew, August 25, 2011 @ 4:51 pm

    Thanks for the blog post just found it via google.

    Do you know of a way to deploy a DACPAC automatically using a continuous integration (CI) build system?

    Can this be done via msbuild, powershell or some other means?

  • By Grant Fritchey, August 25, 2011 @ 4:58 pm

    Excellent question. I’m really not sure, but I’m going to find out.

  • By Andrew, August 25, 2011 @ 8:29 pm

    Thank you. I look forward to what you are able to find out.

  • By Grant Fritchey, August 26, 2011 @ 7:42 am

    Nuts. I was hoping to have another whole blog post to write up from this, but it’s right here:
    http://msdn.microsoft.com/en-us/library/ee210569.aspx

    Nice little PowerShell script.

  • By Andrew, August 26, 2011 @ 1:31 pm

    That’s too bad you couldn’t write something up but thanks for finding that, it’s great.

    Correct me if I’m wrong but the MSDN article you referenced is for deploying a DACPAC which is a “one-time” operation to register and setup the database objects.

    Am I right that for a CI build server you would want to continuously upgrade the database with the latest DACPAC (this MSDN post is the update PowerShell script: http://msdn.microsoft.com/en-us/library/ee634742.aspx)

  • By Grant Fritchey, August 26, 2011 @ 2:38 pm

    With a CI build, it wouldn’t matter that you did a drop & replace, so the DACPAC should work fine. It won’t test your incremental deployments, but that’s not the point of a CI process anyway.

Other Links to this Post

  1. Scarlet & Scary : SQL Azure Deployments — April 25, 2011 @ 11:25 am

  2. SQL Azure and the DAC Pac | Home Of The Scary DBA — May 2, 2011 @ 8:46 am

RSS feed for comments on this post. TrackBack URI

Leave a comment