Managing Databases in SQL Server Availability Groups for Environment Refresh

SQL Server provides Availability Groups for high availability and most of our customers using SQL Server are using this functionality for their Production database.

We also encourage them to have another environment configured to be as 'Production-like' as possible - so we have to manage a test environment that will also be configured to use the Availability Group functions.

As a result, we have developed 3 new steps that can be added to the Environment Refresh workflow, to allow emdash to restore a database that is part of an AG.

The 3 new steps are:

1.  RemoveDBFromAvailabilityGroupOnPrimary 

This step should be added to the workflow directly before the Database Restore step.  It checks to see if the database is part of an availability group and runs a SQL command to remove it.

2.  BackupDBandLOGforAvailabilityGroup 

This step should be run at the end of the workflow - so that it (and the subsequent step below) can run after all environment refresh steps have completed and the environment handed back to the user.

This step checks to see if there is an availability group configured on the SQL Server and takes a full and log backup of the database on the Primary node.

3.  RestoreAvailabilityGroupOnSecondary 

This step should be run directly after the BackupDBandLOGforAvailabilityGroup step - and indeed can be the very last step in the Env Refresh workflow.

This step restores the backup files taken before onto the database on the Secondary node of the Availability group, and then runs a command to reinstate the database synchronisation.

All 3 scripts are attached below.