Thursday, November 18, 2010

Development Database Setup

Hello Everyone,

If you're working on an application of any significant complexity, one of the tasks developers must deal with are database set up scripts.  One trend that I've noticed over the years is that projects where developers maintain a set of scripts that setup the databases from scratch tend to go more smoothly.  To be clear, this process includes tearing down any existing database, rebuilding the database to the most recent release version, loading good test data, and finally running all change scripts for the next release.  Though those last two steps can be reversible depending on the project.

Example the first.  I have a system.  Yaay!  I need to make a change to the database.  It's a relatively complex change involving creating a new table including keys and indexes, copying data from an existing table to the new one, and removing columns on the old table. Booo!  After I make all these changes and have the scripts in place, I need to test that I did everything correctly.  On the plus side, I have a thorough rebuild process in place.  I can click a button and the whole system is rebuilt, I find problems and fix them myself and I know without bothering any other developers that the scripts are good.  Yaay!

Example the second.  I have a system.  Yaay!  I need to make a change to the database.  It's a relatively complex... yada yada yada... I need to test that I did everything correctly  Booo! Unfortunately, databases are not torn down and rebuilt as part of the setup process, they are only modified.  What do I do to easily test my script.  I can undo the changes by hand and rerun the scripts to make sure, or I can check in the changes, hope for the best and let the next developer find any problems and repeat the cycle with yet another developer.  Which one do you think ends up happening more often?  Booo!

This may not be a huge issue that comes up a lot, but when I see emails going out a few times a month with issues lasting a couple of days because things are done the latter, it reminds me of how many things that developers already need to keep track of and this is just another disturbance.  In the end the amount of work to make the changes is the same, but having a complete rebuild is a way to minimize disturbances and keep developers developing.  

If starting a new project, I highly recommend the complete rebuild approach because the hardest possible task would be overcoming momentum and switching from one process to the other.