One of my clients came up with a requirement to use log shipping along with an AlwaysOn Availability Group. They cannot extend an AlwaysOn replica to the disaster recovery site for a few reasons listed below;
- Infrastructure or staffing to maintain WSFC configurations between different sites
- Delayed Recovery – In Log-shipping we can have definite delay on secondary database, SLAs on RPO, RTO force a fast recovery from manual error which only can be realized with delayed recovery restoring the transaction log backups on one instance of the HA/DR strategy
We have two SQL Servers in AlwaysOn AG Group SQL1 and SQL2 in Primary site for HA. One SQL Server instance SQL3 in secondary site for disaster recovery. The primary site is already setup with a two node AlwaysOn Availability Group (AG) with SQL1 as the primary replica and SQL2 as the secondary replica.
In the below screenshot, SQL1 is the primary replica and SQL2 is the secondary replica
To configure AlwaysOn Availability Groups, Reference: http://mssqllover.blogspot.in/2017/02/alwayson-availability-groups-step-by.html
To configure Log-shipping, Reference: http://mssqllover.blogspot.in/2017/03/step-by-step-logshipping-configuration.html
How to setup Log-Shipping on Database part of AlwaysOn Availability Groups
Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers. By default, Alwayson backup preferences are set to prefer the secondary. You have to change backup preference and select as primary. In an AlwaysOn Availability Group, BACKUP LOG supports regular log backups only. COPY_ONLY transaction log backups are not supported on secondary replicas.
Right click on the AG group and select backup preference. Select the primary as the backup preference. In the below screenshot, SQL1 is selected as the primary backup preference.
In SSMS, Connect the primary AlwaysOn Replica (SQL1) with the listener name or listner IP address. In the below screenshot, 192.168.35.17 is the listener IP address and 15333 is the port number.
Now, right click on the database in SSMS and select Properties. Then select the Transaction Log Shipping Page. Check the "Enable this as primary database in a log-shipping configuration" check box. The next step is to configure and schedule a transaction log backup. Click on Backup Settings. Backup Settings (Note: the Backup path\share should be accessible from both the SQL instance SQL1 & SQL2).
In the below screenshot, I have enabled AGL as the primary database for log-shipping and "LS_DC" as the shared backup folder accessible to SQL1 and SQL2.
Next, configure the secondary instance and database. Click on the Add... button to configure the Secondary Server instance and database. You can add multiple servers if you want to setup one to many server log-shipping.
Add the Secondary Server (SQL3) in the DR site and configure the Secondary Database Settings. In the below screenshot, the “LS_DR” folder is for copy.
In the below screenshot, selected database state as standby mode.
Generate the transaction log-shipping dashboard report on the AlwaysOn Primary replica and on the secondary server in the DR site to monitor backup status. You can see both the databases in primary and secondary are completely in sync in the below screenshot.
In the below screenshot, see the AGL database copy and restore status.
To make Log Shipping to work even after a failover of the AlwaysOn replicas in the DC site, right click on the database in SSMS and select SQL1 Properties-> Transaction Log Shipping->Script Configuration-> Script Configuration to New Query window. In the below screenshot, Log Shipping has been scripted out to a new query window.
Now we have to enable Log-Shipping on the Secondary Replica of our AG, i.e. SQL2, so that when AG1 has failed over to SQL2, Log-Shipping continues to work. Right click on AG Group in SSMS and select Properties. Then select failover availability group wizard. Select new primary replica and perform manual failover. In the below screenshot, we are performing an availability group failover.
In the below screenshot, select new primary replica.
In the below screenshot, connect to the new primary replica (SQL2)
In the below screenshot, failover is successful.
In the below screenshot, post failover, SQL2 is now the AlwaysOn primary replica
Now run the first part of the Log-Shipping script, which we have scripted out earlier against the SQL2 Instance. Once that is done, you can now see log-shipping database is sync between secondary replica (SQL2) in DC site and (SQL3) in DR site.
You can disable the LS backup job on availability secondary replica and enable it on primary replica between failovers.
Log-Shipping has been configured on both nodes. Now whether the AG1 is Primary on SQL1 or SQL2, Log-Shipping will continue to work between Primary replica and SQL3 without any manual intervention.
Now we have to disable\enable any job after any AG1 fail-over between SQL1 & SQL2. In the below screenshot, we can see the LS backup status for the AGL database
In the below screenshot, see the LS copy and restore status for AGL database
Ganapathi varma Chekuri
Lead SQL DBA, MCP
Email: gana20m@gmail.com
