How do you handle database change deployments?

Question

We have been discussing database deployment techniques today, having had a couple of recent failures in our current process and having seen situations where we would like to roll back a deployment but the old version of the application had never been tested against the new version of the database.

On one hand, there are Migration-style deployments, where you have a version-up instruction and a version-down instruction (whether those be written in SQL or in your application language) and your app knows which version it needs to get to.

These are simple, and as we won't rollback often, the developers are keen on simple. However, there are risks when you're adding a field/table and that field is populated before you roll back. Or worse, where you drop data which was relevant to the previous version.

On the other hand, we can consider an upgrade, rollback, rollforward approach where rolling back isn't as drastic as with Migrations. For example, upgrade may add a non-nullable field; rollback makes it nullable so that the old app doesn't care; rollforward populates the null fields and makes it non-nullable again.

This retains data but is complicated both to code and to test (sadly, our automated integration tests are pretty much non-existent and while we are correcting that, we have a problem in the meantime).

Are there safe ways to mitigate the problems with these? Are there other options I should be considering? Have you had bad experiences you'd like to share which can save me pain later?

Answer 1

Database changes should be handled like all other changes and deployed as scripts as part of the deployment (and of course saved in source control). Since they are deployed with the code for the same version of application, you know exactly what needs to be rolled back. You can get fancy and write a script to undo each change at the time you write the database script but if rollbacks are not common, you may not want to do that. If a new column is populated you will lose data if you return to the orginal database.

In SQL Server you can take a snapshot just before a deployment and then return to it immediately if the deployment fails. This assumes the deployment is NOT happening when users are on the system (you don't want to lose their data changes). This is most useful during a major release when you may have to take the whole system down temporarilty to do the upgrade. Or you can still take the snapshot and do a database compare between the snapshot and the database to see the differences if you need to rollback. A tool like SQLCompare can even generate the code to get back to the snapshot structure. I don't know what is available for other databases.

Answer 2

Changes to a database structure should be automated/scripted and tested using a test environment. Manual changes are too risky on a production environment

The only reasonable rollback strategy (the one with the least probability of making things worse) is to go back to a pre-upgrade snapshot. If things are going to go wrong it will either happen quickly-enough to make it manageable to go back to the snapshot, or too late for a rollback (the next end-of-week report fails because of problems in the database).

Changes can be done incrementally (like adding a field) and so tested live with less risk than when doing them all in one pass.

With planning, you can make changes to the database to support several upcoming releases, instead of having to confront the software and database upgrade with each release.

Be prepared to be in emergency mode the days following a database upgrade, just as you would if it was a software upgrade.

Resist the urge to patch problems manually. Use your contingency strategy (rollback, snapshot) and then think about why things went wrong before trying the upgrade again.