Is there a standard approach to rolling out database schema changes?

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

There's no standard process because every system is different. About the last thing that I would do if just wrap everything in a single transaction. What happens if I need to move 500 Gigs of data around? That's one massive transaction.

Recently I've been using database snapshots as my rollback.

Basically take a snapshot, make the changes. Delete the snapshot after signoff. If upgrade failed roll back the snapshot and then try again.

It's a lot quicker to roll back a snapshot than to restore the database (assuming that the database is large).

StackExchange Database Administrators Q#16714, answer score: 5