Database Deployment Strategies (SQL Server)

by

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

Leave a Comment