A developer said I should simply wrap all my database upgrade scripts in a transaction. If it fails, just roll back all changes. All my instincts tell me this is wrong, especially when it comes to dealing with large volumes of data and/or procedures and functions.
I typically hand-hold the upgrade process on single instance databases as follows:
- Negotiate a maintenance window
- Prepare upgrade scripts
- Put database into restricted user mode
- Disable scheduled jobs/processes that would normally hit the database during this window
- Perform a full backup
- Apply the upgrade scripts
- Have developer or test team confirm the app operates as expected
- Put database back into multi-user mode
- Release the database for normal use
When it comes to rolling out changes to several hundred instances, however, I have changed my process as follows:
I make the upgrade scripts much more robust: they can be run multiple times on the same server without harm, database version numbers are repected, scripts will terminate if they are run against the run version, etc.
spawn a process for each server (using powershell, osql, etc)
- run the appropriate upgrade script
- report success or failure