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?