Is Database Version Control Worth It?

In this second post on the subject of automatic database upgrading, I discuss the merits of the system whose implementation is described in the previous post.

I won't be circumspect; it is not cheap to make auto-deployment work. It took one programmer two weeks to implement and we probably spend about one day a month maintaining the system. In the end, it comes down to one fundamental factor: the number of environments to which you deploy. But first, the pro and cons.

The biggest advantage of this system is that deployments can be fully automatic. They can be scheduled to happen at a specific time, and everything will get upgraded. No waiting on the DBA, or ensuring the changes are all correct and in the proper spot.

Similarly, the deployment of the new "database version" becomes as solid and consistent as the deployment of the code. The deployment of the database necessarily becomes part of the testing phase of an iteration. This means that deployments are more often successful because they are better controlled and better tested.

The one big disadvantage is complexity. There is a lot of complexity in maintaining a database version. I am not convinced, however, that this complexity is due to the automatic deployment. Rather, I think that the deployment merely exposes problems that would otherwise be hidden when deployment is done manually.

For example, the system may boot fine without running a particular rollback script, but the deployer will stop the boot because the upgrade will fail because the rollback was not run. This would be hidden in a manual deployment, but exposed during an automatic one.

But by far the biggest determining factor is the number of instances you need to deploy to. Just on the development side of things, we have a unittesting database, a deployment database (to unit-test the upgrade scripts), the full development database, the trimmed (lean data) development database and a number of personal development database instances. Then there are testing, staging and production databases.

If a developer makes a change to the database they must publish this change (run it) on each of those databases. If they don't, tests will begin to fail and servers will fail to start as others upgrade to the latest source which does not yet have the required database support. If the developer does not run them on all the databases, it is left to other developers to run the changes when they figure out the reason their servers will not boot.

With the automatic deployment, none of this is necessary. Upgrading to the latest version of the source will also upgrade any databases being used to the appropriate version.

For us, with only a half-dozen databases, it is worth it. It is worth it twice over. I never have to come in during the pre-dawn to launch a version, and I never have to tell other guys they need to run change scripts as they appear. My code changes and databases changes have been harmonized. They both work in the same way.

Everything just works. And that is the best compliment a user can give to a piece of software.