Problem

Automatic database updates in a Continuous Integration Server (i use CruiseControl.NET) has always been a missing link for me. There are two aspects of it:
  1. unit-testing database-dependant classes.
  2. publishing database changes to the staging environment (mainly for web project).
Unit testing database-dependant classes (i use NUnit) can be easily solved by just having our MDF file (in case of SQL Server database) in the source control repository (i use Subversion). Making changes to the database is easy, and those changes will be unit-tested by our CI Server when we comming those changes.
However there are a number of pottential problems/annoyances that come out of this:
  • Can't have more than one person editing the database file - can't easily merge two the binary files.
  • There's no easy way to see what has actually changed.
  • File size can pottentially become quite large.
Publishing database changes to the staging environment sometimes can not be automated by just replacing entire database with the latest MDF from the version control repository, because there may be some data in the staging database that we don't want to loose. So the solution would be to maintain a list of SQL change-scripts (version-controlled) which are then run on the stating database.

But if we have change-scripts anyway, there no point in having the actual database file in version control - it can be reconstructed from the change-scripts.

Solution

So we need an automated way of applying those change-scripts to the test datanase (for unit-testing) and to the staging database.

I have been working on such Database Integration tool for MySQL database, my plan is to upgrade it to support MS SQL.

Here's how it works. I chose to dive my sql script into these 5 categories:
  1. Structure (tables, indexes & relatishipts)
  2. Stored procedures
  3. Static data (data that doesn't change much, such as UserType or Roles)
  4. Test data (dynamic data such as Users or SessionLog)
  5. Updates (each script i sran only once - e.g. alter a table)
The idea behind this split is that scripts i want to run on test database and of stating database are different. Namely, on test database i run all scripts:
  • Structure
  • Stored procedure
  • Static data
  • Test data
  • Updates
and on staging database i run these:
  • Stored procedure
  • Static data
  • Updates
For the categories 1-4 i have one file per table / stored procedure. For Updates i just have as many files as i need. These update scripts must not be modified after committed to the version control repository (because they will not be ran again).

The Update scripts are executed by the tool only once - once executed the file name is logged into the database in "DBChanges" table, so next time we can check if a particular script has been executed.

These update scripts can potentially be granulated into sub-categories:
  • Updates-Common
  • Updates-Test
  • Updates-Staging
  • Updates-Live
So that if there are any specific updates to live database (on top of all other "common" updates) they can be scripted.

So this is an automated Sql Integration Tool a nutshell.

In addition to these this tool can script the database for you (everything except Update).

If you think you might find this tool useful - let me know!
Tags: , , , ,