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?
Best Answer
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.