Automate refresh of a SQL Server database that is part of an Availability Group
We have a SQL Server database that is part of an AlwaysOn Availability Group that has one secondary read-only replica. Every once in a while there is a need to refresh the primary database from another server which requires us to remove AlwaysOn and reconfigure. How can this process be automated?
As you probably know, we cannot restore a database that is part of a SQL Server AlwaysOn Availability Group. We need to first remove the SQL Server database from the Availability Group in order to restore the database. In this tip we look at how we can automate this process to ensure that AlwaysOn is re-established after the restore without manual intervention.
First we walk through the steps that need to take place and then talk about how this can be automated using SQL Server Agent Jobs.
Remove SQL Server Database From AlwaysOn Availability Group on Primary
The first step is to remove the database from the Availability Group. We can achieve this using the below command.
Note: you would need to replace the <Availability Group> and <Database Name> with your values.
-- runs on primary server USE master GO ALTER AVAILABILITY GROUP [<Availability Group>] REMOVE DATABASE [<Database Name>]; GO
Restore SQL Server Database on Primary
The next step would be to restore the backup to refresh the database on your primary server. You can also add additional steps for any specific requirements to run after the restore such as granting permissions, etc.
This is the backup that is coming from the other server that will be used to refresh the database on the primary server.
-- runs on primary server USE master GO RESTORE DATABASE [<Database Name>] FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH REPLACE GO
Ensure Restored SQL Server Database is in Full Recovery on Primary
We need to ensure the database recovery mode is set to FULL in order to meet AlwaysOn requirements.
-- runs on primary server USE master GO ALTER DATABASE [<Database Name>] SET RECOVERY FULL WITH NO_WAIT GO
Backup SQL Server Database and Log on Primary
Next you need to take a full backup and a transaction log backup of this restored database. To make this easier for the restore you should create the backups on a network share which is accessible from both the PRIMARY and SECONDARY servers. This will avoid having to copy the backup from the PRIMARY to the SECONDARY and will also save time by eliminating the copy step.
-- runs on primary server BACKUP DATABASE [<database Name>] TO DISK='<Shared Network Location>\<Database Name>.bak' WITH FORMAT, INIT, COMPRESSION GO BACKUP LOG [<database Name>] TO DISK='<Shared Network Location>\<Database Name>.trn' WITH FORMAT, INIT, COMPRESSION GO
Add SQL Server Database to Availability Group on Primary
Next we need to add the database back to the Availability Group.
-- runs on primary server USE master GO ALTER AVAILABILITY GROUP [<Availability Group>] ADD DATABASE [<Database Name>]; GO
Now you are done with all the steps on the PRIMARY server.
Restore SQL Server Database on Secondary
Next we need to restore the full backup and log backup on the secondary server.
-- runs on secondary server USE master GO RESTORE DATABASE [<database Name>] FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH FILE=1, REPLACE, NORECOVERY GO RESTORE LOG [<database Name>] FROM DISK='<Shared Network Location>\<Database Name>.trn' WITH FILE=1, REPLACE, NORECOVERY GO
Check Status of SQL Server Database and then Add to Availability Group on Secondary
The next step is important, this enables data synchronization after the database has been restored and is ready to join the Availability Group.
-- runs on secondary server -- Wait for the replica to start communicating begin try declare @conn bit declare @count int declare @replica_id uniqueidentifier declare @group_id uniqueidentifier set @conn = 0 set @count = 30 -- wait for 5 minutes if (serverproperty('IsHadrEnabled') = 1) and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0) and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0) begin select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'<Availability Group>' select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id while @conn <> 1 and @count > 0 begin set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1) if @conn = 1 begin -- exit loop when the replica is connected, or if the query cannot find the replica status break end waitfor delay '00:00:10' set @count = @count - 1 end end end try begin catch -- If the wait loop fails, do not stop execution of the alter database statement end catch ALTER DATABASE [<Database Name>] SET HADR AVAILABILITY GROUP = [<Availability Group>]; GO
SQL Server Agent Jobs
To automate this process we can create SQL Server Agent jobs on both servers:
- Primary Server - Create one job that does the following:
- Removes database from Availability Group
- Restores the database
- Sets recovery model of database to FULL
- Adds database to Availability Group
- Creates database backup and log backup
- optional - runs job on secondary server using sp_start_job (there are other things you would need to setup to do this)
- Secondary Server - Create one job that does the following:
- Restores database backup and log backup
- Checks database status and adds database to Availability Group
If you don't have the primary server start the job on the secondary server you would need to figure out when to schedule this job to run after the job on the primary server completes.
- Check out these related tips:
Last Updated: 2017-12-14
About the author
View all my tips