Checklist for Database Schema Upgrades

Question

Having to upgrade a database schema makes installing a new release of software a lot trickier. What are the best practices for doing this?

I'm looking for a checklist or timeline of action items, such as

• 8:30 shut down apps
• 8:45 modify schema
• 9:15 install new apps
• 9:30 restart db

etc, showing how to minimize risk and downtime. Issues such as

• backing out of the upgrade if things go awry
• minimizing impact to existing apps
• "hot" updates while the database is running
• promoting from devel to test to production servers

are especially of interest.

Answer 1

I guess you have considered the reads of Scott Ambler? http://www.agiledata.org/essays/databaseRefactoring.html

Answer 2

This is a topic that I was just talking about at work. Mainly the problem is that unless database migrations is handled for you nicely by your framework, eg rails and their migration scripts, then it is left up to you.

The current way that we do it has apparent flaws, and I am open to other suggestions.

1. Have a schema dump with static data that is required to be there kept up to date and in version control.
2. Every time you do a schema changing action, ALTER, CREATE, etc. dump it to a file and throw it in version control.
3. Make sure you update the original sql db dump.
4. When doing pushes to live make sure you or your script applies the sql files to the db.
5. Clean up old sql files that are in version control as they become old.

This is by no means optimal and is really not intended as a "backup" db. It's simply to make pushes to live easy, and to keep developers on the same page. There is probably something cool you could setup with capistrano as far as automating the application of the sql files to the db.

Db specific version control would be pretty awesome. There is probably something that does that and if there isn't there probably should be.

Answer 3

I have a lot of experience with this. My application is highly iterative, and schema changes happen frequently. I do a production release roughly every 2 to 3 weeks, with 50-100 items cleared from my FogBugz list for each one. Every release we've done over the last few years has required schema changes to support new features.

The key to this is to practice the changes several times in a test environment before actually making them on the live servers.

I keep a deployment checklist file that is copied from a template and then heavily edited for each release with anything that is out of the ordinary.

I have two scripts that I run on the database, one for schema changes, one for programmability (procedures, views, etc). The changes script is coded by hand, and the one with the procs is scripted via Powershell. The change script is run when everything is turned off (you have to pick a time that annoys the least amount of users for this), and it is run command by command, manually, just in case anything goes weird. The most common problem I have run into is adding a unique constraint that fails due to duplicate rows.

When preparing for an integration testing cycle, I go through my checklist on a test server, as if that server was production. Then, in addition to that, I go get an actual copy of the production database (this is a good time to swap out your offsite backups), and I run the scripts on a restored local version (which is also good because it proves my latest backup is sound). I'm killing a lot of birds with one stone here.

So that's 4 databases total:

1. Dev: all changes must be made in the change script, never with studio.
2. Test: Integration testing happens here
3. Copy of production: Last minute deployment practice
4. Production

You really, really need to get it right when you do it on production. Backing out schema changes is hard.

As far as hotfixes, I will only ever hotfix procedures, never schema, unless it's a very isolated change and crucial for the business.

Answer 4

And if the Scott Ambler paper whets your appetite I can recommend his book with Pramod J Sadolage called 'Refactoring Databases' - http://www.ambysoft.com/books/refactoringDatabases.html

There is also a lot of useful advice and information at the Agile Database group at Yahoo - http://tech.groups.yahoo.com/group/agileDatabases/

Answer 5

Two quick notes:

1. It goes without saying... So I'll say it twice.
   Verify that you have a valid backup.
   Verify that you have a valid backup.

2. @mk. Check out Jeff's blog post on database version control (if you haven't already)