Database Deployment Strategies (SQL Server)

Question

I am looking for a way to do daily deployments and keep the database scripts in line with releases.

Currently, we have a fairly decent way of deploying our source, we have unit code coverage, continuous integration and rollback procedures.

The problem is keeping the database scripts in line with a release. Everyone seems to try the script out on the test database then run them on live, when the ORM mappings are updated (that is, the changes goes live) then it picks up the new column.

The first problem is that none of the scripts HAVE to be written anywhere, generally everyone "attempts" to put them into a Subversion folder but some of the lazier people just run the script on live and most of the time no one knows who has done what to the database.

The second issue is that we have 4 test databases and they are ALWAYS out of line and the only way to truly line them back up is to do a restore from the live database.

I am a big believer that a process like this needs to be simple, straightforward and easy to use in order to help a developer, not hinder them.

What I am looking for are techniques/ideas that make it EASY for the developer to want to record their database scripts so they can be ran as part of a release procedure. A process that the developer would want to follow.

Any stories, use cases or even a link would helpful.

Answer 1

For this very problem I chose to use a migration tool: Migratordotnet.

With migrations (in any tool) you have a simple class used to perform your changes and undo them. Here's an example:

[Migration(62)]
public class _62_add_date_created_column : Migration
{
    public void Up()
    {
       //add it nullable
       Database.AddColumn("Customers", new Column("DateCreated", DateTime) );

       //seed it with data
       Database.Execute("update Customers set DateCreated = getdate()");

       //add not-null constraint
       Database.AddNotNullConstraint("Customers", "DateCreated");
    }

    public void Down()
    {
       Database.RemoveColumn("Customers", "DateCreated");
    }
}

This example shows how you can handle volatile updates, like adding a new not-null column to a table that has existing data. This can be automated easily, and you can easily go up and down between versions.

This has been a really valuable addition to our build, and has streamlined the process immensely.

I posted a comparison of the various migration frameworks in .NET here: http://benscheirman.com/2008/06/net-database-migration-tool-roundup