Shared Development Databases

First of all - long time no postee! Sorry about that. As well as starting to arrange my own wedding, I've been helping a couple of friends out with some interview questions and another ASP.NET application. All this adds up to very little time left over for blogging.

Back to your scheduled transmission.

I've been wearing the Enterprise .NET/SQL Server/Oracle developer hat for a little over a year now, and it still isn't very comfortable. One of the particularly uncomfortable aspects is the shared development database that we have to use.

Yes, that's right - everyone develops on a single instance of Oracle or SQL Server. People are making structural and procedural changes all the time as we develop the software. Chaos ensues.

Well - actually, less chaos than you would expect. We have some carefully orchestrated dances which minimise the impact of this restriction. The given reasons for this restriction are of varying levels of convincingness (new word made up today - check):

• Centralised access control
• Centralised maintenance and patching

I can understand those arguments. However, for me, the massive inconvenience and danger of losing work significantly outweighs the benefits (especially considering the access control procedures are laughable - but that's a story for another day).

The rhyme and reason aside, what do you do?

Make sure you have scripts for everything

If you're not physically sharing a database, then you need to be able to share changes. If you don't have this, take a snapshot of your current schema now and check it into source control.

You should then add a table to your database to track schema version (optionally add fields to track who made changes and when, if you like).

Finally, only make changes to the database schema via scripts. These scripts should update the database version table once they've run; they should probably sanity check that the database is at the expected version before running. If you use an appropriate naming convention (perhaps <version>.sql) then it becomes pretty easy to automate running these.

Get Your Own Database Schema

Even if you're stuck on shared kit and database instance, getting your own database schema (or even better, permission to create your own schemas) is the next best thing. You should be using the schema scripts to communicate change. Having an automated database build is really critical here. Wiring that into a continuous integration process (or at the very least, a nightly integration run) will also help you spot any conflicts.

This works best if you're using a branch-for-feature (or branch-for bug) strategy in source control. That way, when your piece of work is done, you drop your development database, rebuild it from trunk code, then merge your scripts into trunk and run them. This merge step gives you the opportunity to reconcile any conflicts.

Of course - it always helps to talk to your colleagues! Even with the best branching and merging strategy in the world, conflicts are going to be difficult to resolve if two of you have created a column with the same name but different types.

What next?

Unfortunately, the above doesn't seem to be possible at The Day Job. We have to share schemas. We can't each have our own because disk is expensive.

(*cough*)

So we all share a single development schema (for each application), on a single database instance.

If you're like this, then you're in 'cooperative multitasking' territory - that is, you have to agree with the rest of the team that, at all times, you work on different parts of the schema and different stored procedures. (It's even slightly worse on Oracle - tools tend to steer you towards scripting whole packages, so the 'atomic unit of work' tends to be much larger on Oracle than SQL Server.)

You can (just about) make this work. It's painful and error-prone though - and one misstep will blow everyone's work away at once.