Database Change Management

A recent tweet led me to an Alt.Net Canada  video - which was discussing the build process, build skill-sets and the complexity of maintaining a large build scripts. In the middle of the video - a guy with a ThoughtWorks t-shirt on mentioned a toolset that amongst other things, helps with database change management (from development, to testing, to release). The project is called Tarantino Database change management  and it looks really good. I LOVE the solution statement... 

• Each developer using their own local database to do their development work.
• Each environment using it's own database. i.e. Development, Testing, Staging, Production
• Each developer maintains his changes locally. When the database changes are ready to commit along side the application source code, the developer follows these steps:
• • Create a change script that wraps all of the database changes into a single transactional change script. A Tool like Red Gate SQL Compare makes this a 30 second operation.
  • Save the change script in to a folder in your source tree call Update.
  • Commit the change script along with the source code that corresponds to the change.
• The continuous integration server detects changes to the source control repository than it:
• • It builds the application code.
  • It executes the applications unit tests.
  • Executes the database create task to create a new database with all of the changes that are in source control.
  • Executes the projects integration (data access) tests.
  • Marks the builds a success when all the tests pass.
• Each developer runs the build script locally after receiving new schema changes scripts from the source code repository.
• The testers, developers, change management managers execute the script using the Database Update (windows form) tool to run the database scripts against the Dev, Test, and staging environments when the environments receive their next update of the source code. 

About 7 years ago - (and still to this day), one of the most useful on-the-spot mentoring exercises I've ever had, was in database change management. A co-worker showed me the process they'd come up with at their previous employer for giving each developer a local database to develop against, using local work files to maintain their changes (SQL scripts) and then a consolidated SQL release script for combining individual changes, all numbered and versioned against the DB which was matched (and backed-up) against the release version of the application (and all under source control).

I've worked this way every since and I've always been able to track schema changes against releases. That said - not too long ago I was in an office where I was actually accused of 'working dangerously' because I refused to use the 'shared' dev database that all the other devs were using. Ok - so if NOT breaking other developers' build environments, and being able to track and even rollback on schema changes  is a dangerous practice - then feel free to call me Danger Man.

Looking forward to taking a closer look at the Tarantino bits...