Deploying and rolling back changes in an Availability Group database

I'm looking for a solution or workflow. Here's how I used to do database deploys when using a stand-alone server:

• Create a snapshot of the production database

• Deploy changes

• Verify that the deploy was successful

• If the deploy was not successful, restore the database from the snapshot.

I would like to perform a similar process on a database in an Availability Group, but restoring from a backup or snapshot is not a desirable option because the database is fairly large and I'd have to take the database out of the AG first. Here's the ideal type of solution I am looking for:

• Suspend data movement in the AG

• Deploy changes to primary replica

• Verify that the deploy was successful

• If the deploy was not successful, rollback the primary replica to the LSN where we suspended data movement and restart synchronization.

Is there a practical way of doing this?

I would use BEGIN/ROLLBACK TRANSACTION, but this is harder to implement with the automated build process, and given that I would first like to inspect the results (from a different database connection) before committing or rolling back.

There's not really a practical method. I can really think of three ways of rolling back a failed deployment:

• BEGIN \ ROLLBACK TRANSACTION: Normally, this is what I'd recommend - it's cleanest, and you wouldn't have to suspend data movement to do it. However, since you need to be able to view the changes from another machine, it's not really an option.

• Rollback script: With this, you pre-script the commands required to return the database to the state it was in prior to the deployment. It's not a true rollback, and it may be impractical on a large database in some circumstances.

• RESTORE DATABASE: As you mentioned, there are hassles for this on a large database in an AG.

StackExchange Database Administrators Q#146259, answer score: 2