Sql-server – Deploying and rolling back changes in an Availability Group database

availability-groupssnapshotsql server 2014

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.

Best Answer

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.
Related Question