Automate refresh of a SQL Server database that is part of an Availability Group

By: Atul Gaikwad   |   Updated: 2017-12-14   |   Comments (6)   |   Related: > Availability Groups

Problem

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?

Solution

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:

1. Primary Server - Create one job that does the following:
   1. Removes database from Availability Group
   2. Restores the database
   3. Sets recovery model of database to FULL
   4. Adds database to Availability Group
   5. Creates database backup and log backup
   6. optional - runs job on secondary server using sp_start_job (there are other things you would need to setup to do this)
2. Secondary Server - Create one job that does the following:
   1. Restores database backup and log backup
   2. 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.

Next Steps

• Check out these related tips:
  • SQL Server AlwaysOn Availability Groups - Part 1 configuration
  • SQL Server AlwaysOn Availability Groups - Part 2 Availability Groups Setup

About the author

Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips