I received an e-mail yesterday from a friend who has been following my posts on version control in Team Foundation Server. He had a question on how to effectively manage changes to database schemas throughout the life of the project. I thought about it over night and I am not sure I have a good answer other than if you are thinking about it, you are probably on the right path.

Suggestions?

Versioning database scripts really isn’t any different to versioning source files in that they adopt the same branching strategy as the source, but there are some extra things you can do to make life better for yourself.

  • Control the scripts; you need to choose the right thing to version control, in this case its the database scripts. I’m not a huge fan of monolithic scripts because they can sometimes be more difficult to merge so consider breaking them up in some way. I would recommend one script per database object and then creating a batch file which executes them in the right order (the batch file should also be version controlled).
  • Control the difference scripts; this is an extension to the previous suggestion. Basically as you do major database releases its not sufficient to just go and hack a change on the creation script – you need to create alter scripts which can be applied in place on top of the old released schema, this should also take into account any data migrations that need to take place. Its a good idea to create seperate upgrade batch files for each version.
  • Put the version in the data; although I don’t always do this one trick is to create a version table inside the database which is a history of the scripts that have been applied to the database. Application code can then query the latest version from that table and assert at start-up whether they are compatible with it.
  • Create the feedback mechanism; if you can, consider writing unit tests which drive your data access layer. Add these tests to your BVTs in Team Build so that when you change the database schema and an automated build is kicked off (hopefully via continuous integration) you will get notified sooner rather than later what data access code needs to be fixed up.

I hope this helps!