If the database changes are substantial enough to break the applications, I would have a new schema setup to have enough time to test carefully and then when ready, point the application to the new schema.
For small changes, we use scripts 'INSTALL_[SCHEMA].sql' and 'ROLLBACK_[SCHEMA].sql' which do everything needed including backups / restores of any tables needed.
This is tested in the DEV/QA environments as needed then run in Prod right before deploying the application changes.
We haven't had high availability requirements and can get away with a scheduled 3 minute outage but this works for us.
The beauty is that if something goes wrong, the rollback script gets us out of trouble.
The beauty is that if something goes wrong, the rollback script gets us out of trouble.
ah, would it be that were true. Suppose you need to roll back until after the next install? I defy you to set up a reliable test plan that will handle the usual series of casual database tweaking popular with modern devs. The time spent developing and assuring the quality of the rollback will be mostly wasted energy when you're faced with rolling back a change which the next N changes are dependent on.
(more) seriously any sane scheme like yours seems to run into trouble when people want to add columns to some base table. Thusly I think "Alter Table" should be a banned operation for devs in any live system. But it's great job security for a DBA (speaking as a DBA, btw).
For small changes, we use scripts 'INSTALL_[SCHEMA].sql' and 'ROLLBACK_[SCHEMA].sql' which do everything needed including backups / restores of any tables needed.
This is tested in the DEV/QA environments as needed then run in Prod right before deploying the application changes.
We haven't had high availability requirements and can get away with a scheduled 3 minute outage but this works for us.
The beauty is that if something goes wrong, the rollback script gets us out of trouble.