Alexander March 24th
2008

Database Continuous Integration in .NET

In this post I’m going to share my experience on database continuous integration in .NET. What is continuous integration? Well, that’s something that makes your life as a developer much easier.

At Wildbit, we use Cruise Control .NET (CCNET) widely to automate deployments. CCNET gets the latest version from our Beanstalk repository, compiles all the projects inside a solution, makes appropriate updates in configuration files, and publishes the built version to the server. What did I miss? Yes, database updates. That’s an important and not so easy part of deployment, especially if you have a database with data.

Because a database already has data we cannot use declarative SQL scripts where all the tables, procedures, indexes, keys and other database elements are described. We cannot drop the current tables and create new ones because we would lose the data. That’s why we need to use update scripts where the changes to the database are described.

To track those scripts we need an additional solution, an application that will manage information about previously applied update scripts and install the new ones. To store this information I’m going to use the database that we’re updating. Only one table is needed with the following columns:

Primary key
Script file name
Hash of script file name. It will help to determine whether there are any changes after the last execution
Update date and time
Error message. This information will help to find out which scripts was not installed properly and why

Talking in T-SQL language we need the following:

CREATE TABLE [dbo].[DatabaseUpdates](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ScriptName] [nvarchar](256) NOT NULL,
    [Hash] [varbinary](64) NOT NULL,
    [UpdateDate] [datetime] NOT NULL,
    [ErrorMessage] [ntext] NULL,
    CONSTRAINT [PK_DatabaseUpdates] PRIMARY KEY CLUSTERED (
        [ID] ASC
    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Certainly database updates should be a part of the whole process of deployment. It has to stop a deployment if at least one update script fails. A deployment should be like one transaction having ACID properties — atomic, consistent, isolated and durable. To achieve this we should think how a database updater application will communicate with CCNET. I’m going to use language of exit codes. In the CCNET there is an Executable task that allows running console applications. To determine whether the application succeeded or failed CCNET checks the exit code. For instance, on Philly2Night I use the the following block in CCNET configuration as a first task after checking the subversion repository for updates and before calling NAnt jobs:

<project name="Philly2Night">
  ...
  <sourcecontrol type="svn">
  ...
  </sourcecontrol>
  <tasks>
    <exec>
      <executable>DatabaseUpdater.exe</executable>
      <baseDirectory>[path to DatabaseUpdater installation folder]</baseDirectory>
      <buildArgs>-update</buildArgs>
      <buildTimeoutSeconds>360</buildTimeoutSeconds>
      <successExitCodes>0</successExitCodes>
    </exec>
    <nant>
    ...
    </nant>
  </tasks>
</project>

As you can see I specified only “0” as a success exit code. If the Database Updater cannot execute one of the update scripts it will return exit code “2” and CCNET will stop deployment before the project will be compiled and published on the web.

To start using the Database Updater you need to setup the database, which means create a database table [dbo].[DatabaseUpdates] that I described above and add information about the scripts that you probably already installed manually. Do not worry, Database Updater will do it for you. Just use the command:

DatabaseUpdater -setup -no_execute

“-setup” means adding database schema that needs Database Updater to work with database and “-no_execute” means add all scripts that you have without actually executing them.

But before this let’s edit Database Updater configuration file — DatabaseUpdater.exe.config. Two things should be considered — path to folder where you store update scripts (somewhere in the source control imported project folder) and connection string to the database.

After setting up the Database Updater you can have it update the database using this command:

DatabaseUpdater -update

Or add Executable task in CCNET like I described above. The latest version of Database Updater and sources canbe found on Codeplex. Please notice also that Database Updater works with any database that .NET Framework supports. Now you can enjoy coding and let continuous integration do the other stuff!

Some associated links: