Database Deployment Strategies (SQL Server)

Question

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

Read K.Scott Allen's series of posts on database versioning.
We built a tool for applying database scripts in a controlled manner based on the techniques he describes and it works well.
This could then be used as part of the continuous integration process with each test database having changes deployed to it when a commit is made to the URL you keep the database upgrade scripts in. I'd suggest having a baseline script and upgrade scripts so that you can always run a sequence of scripts to get a database from it's current version to the new state that is needed.
This does still require some process and discipline from the developers though (all changes need to be rolled into a new version of the base install script and a patch script).

We've been using SQL Compare from RedGate for a few years now:

http://www.red-gate.com/products/index.htm

The pro version has a command line interface that you could probably use to setup your deployment procedures.

We use a modified version of the database versioning described by K. Scott Allen. We use the Database Publishing Wizard to create the original baseline script. Then a custom C# tool based on SQL SMO to dump the stored procedures, views and user functions. Change scripts which contain schema and data changes are generated by Red Gate tools. So we end up with a structure like

Database\
    ObjectScripts\ - contains stored procs, views and user funcs 1-per file
    \baseline.sql - database snapshot which includes tables and data
    \sc.01.00.0001.sql - incremental change scripts
    \sc.01.00.0002.sql
    \sc.01.00.0003.sql

The custom tool creates the database if necessary, applies the baseline.sql if necessary, adds a SchemaChanges table if necessary and applies the change scripts as necessary based on what's in the SchemaChanges table. That process occurs as part of a nant build script each time we do a deployment build via cc.net.

If anyone wants the source code to the schemachanger app I can throw it up on codeplex/google or wherever.