Most business applications are fundamentally database applications. A business application's database is as much a part of the application as its C# source code. Unfortunately, in many development shops versioning and controlling the database is done very differently from the in memory source code. For example:
- Often a single 'development' database is shared between the developers. This means that if a developer makes a schema change he runs the risk of breaking his colleague's environments at least until the next build. Often this leads to a fear of changing the database schema.
- The database is often versioned independently of the in memory source code. This makes is hard to deploy a specific build because often the database backups and the in memory source are are not syncronised.
- The database is often not source controlled, or if it is, it is done as a single build mega-script. This makes it impossible to package linked database and in memory source changes as a single 'feature' or 'fix'. It makes it hard or impossible to roll out a single failing feature. Also, because the mega-script is often created by choosing the 'script database' feature of the dbms it is checked into source control under the build manger's login or whatever login the build scripts are running under. This means that it is impossible to track database changes. I can't go to a single table's create script in source safe, look for the last time it was checked in and find out who made the change and then read the comments to found out why it was changed. The best I can mange is to look through every single version of a huge script file looking for a change, but then there's no way of knowing who made it or why it was made.
How should you manage your database development then? Well the best thing is to treat it as much as you can just like all your other source code:
- Every developer should develop against their own database instance.
- A database backup should be stored with the executables from the build. That database backup should be a backup of a database built from the sql scripts retrieved from source control at the same label as all the other source.
- The database should be maintained as object scripts. Each table, stored procedure, view and function should have its own script. To make any change in their local database a developer should check out the object they want to change, apply that to their database, unit test the change and then check in the change as a package with any other source for that feature or fix. The developer should label the package and comment it with a link to a feature or defect number.
- Before a developer starts work on the next feature or fix he should get the source from the last build label (which, with continuous integration should also be the latest version) and restore his local database from the same labelled backup.
In order to do this you need tools that allow you to do the following:
- Build a database from object level source files. Most dbms will choke if you try to just run the scripts in without first working out a build order by examining the object references. The tool must automate this for you, it's far to onerous to try to do this manually.
- Be able to upgrade a database to a new schema version by calculating 'alter' scripts.
Here's where I plug a product that a couple of guys I know and worked with at NTL have developed, DbGhost. It allows you to adopt all those good practices that I've listed above. I've got it adopted on several projects now and they still haven't given me any commission or offered me a lucrative consultancy contract:(