SQL Version Control

Practice Safe SQL Server Version Control ( Part #1), (part #2)

While it’s obvious for any developer that some sort of version control\change management solution is needed on every database related development project, very few shops properly version their databases. This seems like a strange situation, at first. The database schema, which includes tables, views, procedures etc., is just as vital as the application code. Why is it being neglected so often?
Some readings of articles and discussion groups online shows that many people are in fact aware of this situation, but the main problems seems to be the lack of available tools in the market. While the version control\change management market is flooded with solutions - be it open source like Subversion, or a commercial product like Microsoft Team Foundation Server – there are very few solutions out there for versioning databases. Faced with this reality, most software shops adopt one of the following (if at all):

1. Simply relying on database backups

As strange as it sounds, database backups is what most companies out there have as far as versioning their databases. True, It is possible to restore the database, but its time consuming, resource intensive (both human and machine) it doesn’t catch changes done in between backups, and if one is looking for one specific change only it’s not always easy to find.

2. Periodically script the database and check the resulting files in the repository.

This is the most basic solutions for those who decide to tackle this problem. With its main upside being its simplicity, and the downside being that it requires some effort and a certain measure of responsibility on the part of the person in charge of implementing it; finding specific change is not always simple (since it requires many searches on plain text files) and rolling back is still tricky in some cases – for example, when dealing with complex table changes

3. Using custom TSQL Script to automate scripting and checking into a Version Control System

This is an upgrade on the previous solution. Many suggested automation scripts are available out there. They detect a change and script it out to a file. While this saves some human labor and resulting in smaller scripts, the problems mentioned above are still there. And automating a push to the existing versioning system is not always easy (depending on which system is used)

4. Using Database Comparison Software to do versioning

There are many popular database comparison and migration tools out there. Some of them are quite good at what they do, but the problem is, this is not what they were built to do. Having a full version control solution implemented using a database comparison tools is far from intuitive. Not only it requires a lot of thinking and planning in order to set up, once they are up they usually require a lot of human intervention to bridge the gap between simple database comparisons to full change management solution.

5. Implementing one of the database version control products on the market today

Recently, few vendors started coming out with custom tools for SQL Server versioning. But these tools still suffer from at least the following problems:
• They are not easy to set up
• They are rather light on features
• They have a rather heavy impact on the servers they monitor
• They don’t integrate with existing version control systems
• They are extremely expensive. Many of them charge by each database they monitor, which could mean an expense of thousands of dollars.

End of part 1, part 2 will discuss the solutions.