DAC deployment and Windows Azure.

So here’s a nifty little feature. Visual Studio has a project type for SQL Server database projects. You write some scripts to create your database;

CREATE TABLE [dbo].[Customers]
(
 [Id] INT NOT NULL PRIMARY KEY, 
 [Name] NCHAR(255) NOT NULL 
)

Now that’s fine and dandy. You design your database and label it version 1.0.0. You build the project and it creates a ‘.dacbac’ file — a package which can be installed to Windows Azure to create your database. All is good, and your cloud app works happily on top of your database.

But let’s say the next version of your software requires some datbase changes. You update your creation script in SQL Server and label the version 1.1.0, a .1 incremental release.

CREATE TABLE [dbo].[Customers]
(
 [Id] INT NOT NULL PRIMARY KEY, 
 [Name] NCHAR(255) NOT NULL, 
 [Expired] BIT NOT NULL DEFAULT FALSE
)

Now you go ahead and build again. You get a new .dacbac file. When you try to upload the new definition, The Windows Azure service scans the SQL script, notices the columns, compares it to the existing database, and generates a change script which will upgrade from 1.0 to 1.1.

Now that’s really snazzy. Previously, developers would exect to write ‘patching’ scripts; something starting

-- 1.0 to 1.1 upgrade script
 ALTER TABLE [dbo].[Customers]
 ...

but notice that no such ALTER statements are being made. It’s just two CREATE statements, being intelligently compared. I asked Nino Filipe, and he confirmed that the service itself is actually parsing the file, figuring out the effect, comparing it to the current state, and generating a new script with the ALTER statements itself, so you don’t have to.