Continuous Integration Tip #3 – Version your Databases as part of your automated build

Having a good Continuous Integration setup is the gift that keeps on giving, but what about your database? For most web applications these days, your database is a large part of your application – so why is versioning it such an uncommon thing? Because it’s time consuming and complicated – two requirements that Continuous Integration love conquering with a one-two punch.

This post is part of a series on Continuous Integration Tips & Tricks in follow up to a post i made about setting up Continuous Integration Automated Deployment with Web Deployment Projects & Team City.

Tools of the trade

Starting shortly after the release of SQL 2005, Microsoft created something call the SQL Publishing Wizard (the link is for an old version for working with SQL 2005). As time went by, and this tool started to get good recognition, an effort was made to integrate this into SQL itself – this is what we now know as the “Generate Scripts” option in SQL Management Studio.

This is also now integrated into Visual Studio 2008 under the moniker “Publish to Provider”. One of the coolest things about the tools that is used is that it has a bunch of command-line options that make things really interesting when it comes to putting this into a continuous integration environment. This is what we are going to take a look at today.

Get the party started

While i know that you are probably, dear reader, like me, cringing at my use of a crappy Pink pop song lyric in a blog post about Continuous Integration (for no reason but to have a title for this paragraph), i must caveat this post by saying that i will only be showing you one example that uses the SQL 2008 Client Tools.

This means that in order for this to posts’ magical juju to take effect, you must install the SQL 2008 client tools on your build server, so that the command line exes match the ones I'm using – you should be able to modify my demo slightly to better fit your setup if you are using the older Version 1.1 of the tool for SQL 2005, but my caveat still stands:

Your Mileage may vary

Downloads:

SQL 2005 Database Publishing Wizard 1.1
SQL 2008 Database Publishing Wizard – you don’t need it, cause it’s built in!
MSBUILD Community Tasks
Subversion windows client

Our process will follow a roadmap:

Run the SQL Publishing Wizard’s command line exe against our respective database details, and save the output to a local path in our build output directory.
Save the above path back into SVN using MSBUILD community tasks (you will have to change this step if you are using a different Source Control provider like Team Foundation Server, Git, or Mercurial you’ll need to find an appropriate MSBUILD task to run it)

I’m gonna version me some databases

Install Microsoft SQL 2008 client tools (you don’t need the whole database server installed) on the machine you are going to be testing this MSBUILD script on. You will also need to install the client tools on your final build server.

Add the following to your current build script or web deployment project build file replacing the parts in bold with your database servers details.

In your web deployment build script add the following:

    <Target Name="VersionDB">
        <!-- Set the path to your SQL Publishing Wizard -->
        <PropertyGroup>
            <PathToSQLPublWiz>"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.4\sqlpubwiz.exe"</PathToSQLPublWiz>
            <DatabaseConnectionString>"Data Source=MYSERVERADDRESS;Initial Catalog=MYDATABASENAME;User ID=MYDATABASEUSER;Password=MYDATABASEPASSWORD"</DatabaseConnectionString>        
        </PropertyGroup>
        <!-- Convert the output path to an absolute path -->
        <ConvertToAbsolutePath Paths="$(OutputPath)">
            <Output TaskParameter="AbsolutePaths" PropertyName="OutputPathAbsolute"/>
        </ConvertToAbsolutePath>
        <!-- Fire Sql Publishing Wizard with our settings -->
        <Exec Command="$(PathToSQlPublWiz) script -C $(DatabaseConnectionString) -f -q $(OutputPathAbsolute)SQLBuildScript.sql"  />
    </Target>

If we want to walk through the build script, it basically says:

Set the properties for my database connection string and the path to SQL Publishing wizard
Convert our web deployment project output path to an absolute path
Call the SQL publishing wizard and save our file in [build output root]\SQLBuildScript.sql

There are a few command line options that the SQL Publishing wizard can take allowing you to generate scripts for different things:

-schemaonly This does exactly what you think and only versions the schema of the db

-dataonly The same as above, but only captures the data

-f This overwrites any existing file with this path

-noschemaqualify This won’t include the schema in the generated script objects

My example above takes a dump of the whole database including data – usually this is not what you want to do as you just want the schema changes (not your 1,000,000 product table rows). For this you should use the –schemaonly switch on the EXEC action.

Checking the SQL script back into Source Control

Now we simply need to check this generated SQL script back into our source control for versioning. MSBUILD doesn’t know what source control you are using, and also doesn’t have any built-in support for most source providers. I’ll show you a quick demo using Subversion and the MSBUILD community tasks, but you’ll have to go searching for your own MSBUILD tasks if you want to deviate from my example and use “what ever the cool kids are into these days”.

I recommend you check out the MSBUILD community tasks I'm using in my example as it has support for:

Visual Source Safe
Team Foundation Server
Subversion

To check your new SQL script into SVN after generation simply add the the following to our example:

<ItemGroup>
    <ToCommit Include="$(OutputPathAbsolute)SQLBuildScript.sql" />
</ItemGroup>
<SvnCommit LocalPath="$(OutputPath)"
                        ToolPath="C:\Program Files (x86)\Subversion\bin\svn.exe"
                        Targets ="@(ToCommit)"
                        Username="MYSVNUSERNAME" 
                        Password="MYSVNPASSWORD" 
                        RepositoryPath="https://mysvnroot.com/mypath/trunk"   />

This would make our final task look like this:

<Target Name="VersionDB">
    <!-- Set the path to your SQL Publishing Wizard -->
    <PropertyGroup>
        <PathToSQLPublWiz>"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.4\sqlpubwiz.exe"</PathToSQLPublWiz>
        <DatabaseConnectionString>"Data Source=MYSERVERADDRESS;Initial Catalog=MYDATABASENAME;User ID=MYDATABASEUSER;Password=MYDATABASEPASSWORD"</DatabaseConnectionString>
    </PropertyGroup>
    <!-- Convert the output path to an absolute path -->
    <ConvertToAbsolutePath Paths="$(OutputPath)">
        <Output TaskParameter="AbsolutePaths" PropertyName="OutputPathAbsolute"/>
    </ConvertToAbsolutePath>
    <!-- Fire Sql Publishing Wizard with our settings -->
    <Exec Command="$(PathToSQlPublWiz) script -C $(DatabaseConnectionString) -f -q -schemaonly $(OutputPathAbsolute)SQLBuildScript.sql"  />
    <!--<Exec Command="$(PathToSQlPublWiz) script -C $(DatabaseConnectionString) -f -schemaonly -dataonly -q $(OutputPath)SQLVersion"  />-->

    <ItemGroup>
        <ToCommit Include="$(OutputPathAbsolute)SQLBuildScript.sql" />
    </ItemGroup>
    <SvnCommit LocalPath="$(OutputPath)"
                            ToolPath="C:\Program Files (x86)\Subversion\bin\svn.exe"
                            Targets ="@(ToCommit)"
                            Username="MYSVNUSERNAME" 
                            Password="MYSVNPASSWORD" 
                            RepositoryPath="https://mysvnroot.com/mypath/trunk"   />        
</Target>

Again basically what this says is:

Add our SQL script to an MSBUILD item collection
commit the file to the SVN path of my choosing using the user details I've provided.

Simple and easy!

Things to think about…

My example above is just the start, if you wanted, you could take this further and create a build task to upload and run the script on your remote staging database to always keep everything up to speed.

You could also quite easily get the build script to pull your connection string from your web.config file using a regular expression match and run it against this – i usually don’t do this as i may be using a different database on my source control working copy, but if you only have a single database this would make the setup even sweeter…