Jeff Atwood wrote about databases under version control in a recent blog posting. In it he advocates that you should put the goal of getting your database into source control above things such as what tools and processes to us as per the Agile Manifesto.

We have met this goal, and our database is under version control, however, I feel it has become a cumbersome and time consuming process (although my I have a lot shorter time working with the process then the other developers so maybe I'm missing some tricks). I have been thinking of ways to improve upon the process.

Also in Jeff's post, are links to a series of 5 posts by K. Scott Allen in which he explains how he does database version control. Some of his points match up with what I have been thinking, some don't. That, in and of itself further proves a my idea that no process is perfect, and what works for someone may not work exactly for you.

K Scott Allen's Posts:

1. Three rules for database work
2. The Baseline
3. Change Scripts
4. Views, Stored Procedures and the Like
5. Branching and Merging

I recommend that interested parties take the time to read the posts, as what follows is a brief overview.

• 3 rules for databases: Never use a shared database for development work, Always have a single schema source (source control), Always version your database.
• Create a baseline script. This is a single script for your tables. Your derived objects (stored procedures, views, etc).
• Change scripts are used to update the baseline, and should include an insert statement into your custom version table.
• Derived objects (stored procedures, views), are updated, by making changes to the individual file corresponding to the objects. These changes are checked into source control. All derived objects are dropped and recreated from the script files. If you add a new object, create a new file, if you need to delete an object, delete the file. Once thing I don't understand, is how are these "versions" related to the schema version? One of the points made is that you should be able to create a database from any point in time. This process covers the schema well, but you would have to hunt thru source control to find the collection of files that relate to that version. Perhaps this is a necessary trade off.
• Branching and merging depend on how you branch your application. The example given, is that when it gets close to a release, functionality is frozen and a branch is made for the release. Only bug fixes go into the branch and new features for the next version go into the main trunk. For the database, this means a new baseline script is created for the new version, which is a point in time snapshot of the database at the time of the branch. If there are changes in the branch, those changes are duplicated as a change script in the main trunk. You just have to be careful that the main trunk script is written in a way that it checks to see if the branch script has been run. (See the actual post for a better explanation).

Finally, I somehow got to this post by Phil Haack, in which he describes how using the information_schema views (see his post, and Books Online for a listing of what you can get from the views) will help future proof your scripts, because as Phil points out, Microsoft makes no guarantees on the schemas of the system tables.

As I posted before, I'm now heading up our embedded device project, so I won't be working on our uber back-end database as much. While we have a need for a database in the embedded project, I'm strongly considering using DB for Objects (db4o). While there is a bit of a learning curve, it looks pretty easy to use.

My plan is to use db4o to start out and then re-evaluate our database needs at the end of the project, hopefully saving the time it takes to manage a data access layer, scripts, etc. This would also make a cost benefit analysis of db4o easier, as I can simply estimate the time it would take to implement a traditional RDBMS and compare that to the cost of db4o.