Over the last year, I've been doing a lot of work on Pay It Square and Tourneytopia lately. We're building new features, iterating on existing features, and fixing bugs at a fairly rapid pace. As a result, we've been deploying new code quite a bit.

Changing code is easy - just upload new binaries and we're all set. But what happens when we update the database? We're a small team, so our first attempt was rather manual: email the team telling them what they needed to update in their database. Actually, because of the pain of updating databases, we used a central, shared development database (which caused issues of its own).

Eventually, we grew up (a bit), and added a SQL file called nextDeploy.sql where we would script out our changes. This file got checked in, and you'd run it whenever you got latest. We tried to keep our scripts idempotent, so running it over and over wasn't a problem.

Then, during deployment, we'd run the script against the production database, and move the scripts in the file to archive.sql, and start all over. This worked better, but there was still a manual process during deployments of running the sql and moving files around. Oh, and working in a branch? Yeah, that's a problem too.

After a bit of pain doing it this way, we eventually decided we needed a better solution. Enter Migrator.NET. Now we write our migrations in code, and can run them against the database to ensure it's up to date. This solves a couple of issues for us. First, we no longer have to use a shared database because keeping our local database up to date is as simple running the migrations against it. We also don't have to maintain SQL files and know what's been ran against which environment.

But just being able to run the migrations on demand wasn't quite good enough. It would still require a deployer to log in and run the migrations after the code was updated on the server. So we took it one step further, and made the migrations run when the app starts up.

It took a little digging to figure out how the command line that Migrator.NET uses to run the migrations, but I eventually figured it out, and extracted it out into a function:

Running this code will migrate a Sql Server database to the latest version. This relies on the migrations being in the same assembly as this method, which for us is exactly what we did. Then you just call this method during application start up, and your database is guaranteed to be up to date after the application starts up.

There are definitely downsides to this, and some things to be aware of. First, your migrations have to work. This seems obvious, but it's important. If the migration fails, the application won't start up right and your database will be out of sync with the application. This means that if you're doing as simple as dropping an index, you better be sure that the index names are the same in your dev database as they are in the production database. This is easy to ensure if you start using migrations from day 1, but when starting with an existing database (like we did), you have to be a little bit more careful when writing them.

Second, the migrations should be pretty quick to run. If a migration takes 5 minutes to run, your users will be sitting at the site waiting for the app to come up for a while. Your scripts could also timeout. In cases like this, we usually take the app down with a friendly message, migrate the database manually, and then bring the app back up. It's not a very elegant solution, but for the most part, our migrations are quick - add a column here, rename one there, etc. We try to deploy often, so there's not normally a lot of changes for each deployment, so we rarely run into this issue - which is why we haven't optimized it much more than that.

While I'm sure there are more downsides, we haven't run into anything serious so far, and it's worked out great for us. Having said that, I'd love to hear from you what you use to manage your database updates.