Mike Schinkel asked me the other day how we do database deployments.

I started thinking and realized it breaks down into two scenarios:

1. Clean Build
2. Version Update Build

#1 is pretty easy to describe.  We script everything.  I am way to paranoid about temporary changes getting made in development that accidentally make their way into production to allow anyone to just replicate databases, use DTS jobs, etc.

All changes must be scripted, all scripts must be in source control, the database is build out of this source tree.  Typically the scripts are broken down like this:

• .sqt files which define the table structure
• .sqx files which define the indexes and primary keys
• .sqp files which define the stored procedures.  There is one file per sproc
• .sqd files which insert initial default data
  

One of my current clients has an implementation of this that as part of the nightly build process it goes through a node of the source tree and runs all .sqt files to put the base schema in place.  Followed by the sqx, sqp and sqd files.  The only problems come when the sqd files come into play and they attempt to insert default data in an order not allowed by constraints, etc.  Here we have a system whereby we can impose an order in which certain ones run.  Awkward at best but it works.

#2 is even more awkward.  In the case of #2 there is existing data in the database that needs to be preserved. As such we have .NB files that are used to make changes to an existing database.  These allow us to for example instead of doing a create table use alter table to add a column.  This is where it gets really hard.  Figuring out what to run and what not to run to preserve data already in the system.

I am suprised that I don't see any tools/nant steps to help automate this type of build.  It is clearly an essential part of the process.

Monday, March 15, 2004 9:56:46 AM (Pacific Standard Time, UTC-08:00)         Comments [0]