One of the challanges that I have been facing over
the last couple of months is how to deal with changes to databases over the
course of project.   To help you understand my problem let me tell you
how our current mechanisim works. 
Current Process
Our current
process uses numbered change scripts which at release time are compared against
the databases.     For example say that the release
directory contains 4 scripts and the table in the database contains the version
number 2.  The directory would look like this:
  • Script1.sql    
  • Script2.sql    
  • Script3.sql    
  • Script4.sql    

At the time release is performed scripts 3 and 4 will
be executed against the database.

Whilst this was working quite well when you start
running a multiple branch strategy and have mutilple projects wanting to make
changes to the same database on different release cycles, this mechanism starts
to become unravelled rather quickly.   The process of merging changes
from one branch to another becomes increasingly difficult and more manual. 
This process also doesn’t allow for different projects to release changes
independently of each other. 
As I started to notice this occuring I thought I
would ask Mitch Denny for some advice
which he kindly posted herehere.    Now
most of the things that he mentioned we are currently doing in some shape or
form.  However, after reading the post it got me thinking what types of
database changes are there?  Essentially there are three:

  • Schema Changes; Changes where tables,views,udt’s are
    added, modified or deleted. 
  • Data Changes; Changes where reference data is added,
    modified or deleted
  • Code Changes; Changes where functions, stored procedures
    and triggers are added, modified or deleted

Of the three types of changes the first one
definately needs to be run in a specific order, the second one might need to be
run in a specific order whilst the third type could be run in any
order.  Also, thinking about this has also started me thinking
about why you would treat database code any differently then
C#.   Essentially, it’s all code. You need to version it,
track changes and release it.  The only difference between database
changes and C# code is that most times you need to preserve the data
when you are making changes to it
.  So I’ve started looking at
changing our process so that we can treat schema, data changes and stored proc
like code.  Also, I’ve started looking at a process that will enable
database changes to packaged up independently of each other and released in one