Managing Databases in .NET

Migrating databases has always been a pain the ass. In the past, I've used tools like RedGate SQL Compare to get the job done. The last major project I worked on (with a team of a dozen developers) we used a custom solution of managing sql updates and change scripts. Nasty business. The main issue with updating databases is not only making sure that your test database matches your staging database which matches your live database, BUT that the database of each instance matches the code running alongside that database. Regardless of which method you use the code-sql versioning has always been a pain. I can't count the number of times an SQL update has been applied when the relevant code patch hasn't! This problem is magnified when you are working in an "Agile" environment. When you're making frequent code updates and with a scope that is constantly changing, databases changes unfortunately are a necessary evil. The Ruby crowd have been using database migrations for some time now, and it's only recently that the .NET community is catching up. I've recently begun using MigratorDotNet. It's not perfect, and is still fairly immature, but it makes managing databases a lot easier than keeping track of change scripts! My particular implementation probably isn't the most recommended method...but it works for me and it saves a lot of messing around with build targets and project configurations. If you haven't already, take a look at the wiki and have a read of the "getting started" page...cos I'm going to assume you know what this code does.....
[Migration(1)]
    public class _001_Init : Migration
    {
        public override void Up()
        {
              Database.AddTable("dbo.tbl_Tokens",
              new Column("TokenId", DbType.Guid, ColumnProperty.PrimaryKey),
              new Column("UserId", DbType.Guid, ColumnProperty.NotNull),
              new Column("DatePurchased", DbType.DateTime),
              new Column("DateUsed", DbType.DateTime)               
          );
         }
     }
Where I change my process is how I set up my projects and how I run my migration. This is for a web application obviously btw. Do this much (taken from the getting started Wiki): 1. Add a new class library project. In this example it's called Called DBMigration. 2. Create a lib directory in your DBMigration project, and extract all the dotnetmigrator DLLs into it. You can exclude database-specific DLLs that you don't need. e.g. If you're not using Oracle, you don't need Oracle.DataAccess.dll. 3. In your DBMigration project, add a reference to the Migrator.Framework.dll. That's the only additional reference you need in the project. Now....Ignore the rest of the steps, and do this instead :) 4. Create your first migration class (like the one above). 5. In your web application, add a reference to Migrator, Migrator.Framework and your DBMigration project. Here's the trick for the lazy peeps that can't be bothered messing with MSBuild and build targets! In your Global.asax file... you want something like this:
protected void Application_Start()
 {
            string strConnString = ConfigurationManager.ConnectionStrings["AspNetSqlProvider"].ConnectionString;
            Assembly a = System.Reflection.Assembly.Load("DBMigration");

            Migrator.Migrator m = new Migrator.Migrator("SqlServer", strConnString, a);
            m.MigrateToLastVersion();
}
Whenever you update the code for your application, on first run the application will automagically update the database using your migration scripts! Nifty! A few important things to take note of here! The first thing to understand is the connection string. I set up my connection strings using an external XML which is referenced from web.config....like this
<connectionStrings configSource="connections.config"/>
And in my connections.config file I have the connection string for the Live OR test OR staging server. There is different version of this file on each respective server (thus make sure it's not in your solution if you use VS deployment!). Why? So that when I deploy my application to the test server, or to staging, it will be updating the database for THAT server only. This means I have a seamless means of updating my database at the same time as updating my code. No batch files, no configuration, no build targets! Just run it, and it updates the right database! Now, obviously this is NOT ideal for all situations. You'd really need to be sure that this approach is suitable for what you need. For us, we do infrequent updates to the live server, but are constantly updating our staging server. This approach means that whenever we deploy and run the website, we know the database is going to be update and correct!