Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2017-12-14   |   Comments (3)   |   Related Tips: More > 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


Last Updated: 2017-12-14


next webcast button


next tip button



About the author
MSSQLTips author Atul Gaikwad 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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, October 30, 2018 - 9:20:12 AM - SQL Server Admin Back To Top

 

Why you need another full backup from primary? You can directly restore single backup from prod to primary and secondary and then simple join.


Monday, October 22, 2018 - 7:37:41 PM - Sam Back To Top

 Great article, very helpful and well-documented steps to follow. Thanks a bunch! 

 


Thursday, September 13, 2018 - 12:20:10 PM - Mat Back To Top

Thank you for the scripts! This post was very helpful and it worked the first time, no adjustements needed.


Learn more about SQL Server tools