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:

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:

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.

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

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

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:

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.