Scenario: Consider we have a primary data center where we want to configure HA solution using Always On availability group and want to configure log shipping to DR site as a DR solution. In this post we will discuss on how to configure log shipping for a database involved in AG and some limitations and manual intervention required with this solution.
Configuration: Suppose we have AG configuration between SQLInst1 and SQLInst2 SQL instances and listener name is SQLAG1 . SQLInst1 is current primary replica. SQLDRInst is the SQL instance where we want to configure log shipping secondary database. It should be setup in such a way that whichever instance AG listener is pointing to, log shipping should keep working fine.
Configuration of log shipping for a database DB1 in AG can be done by following below steps:
- Create a shared folder which should be accessible from all three servers SQLInst1, SQLInst2 and SQLDRInst.
- Connect to primary replica SQLInst1 using AG listener name “SQLAG1“ in SSMS.
- Go to database properties and configure log shipping for database DB1. This would be same as we do log shipping on an standalone instance. Provide shared path while configuring LS backup job.
- Once log shipping is setup between SQLInst1 & SQLDRInst, go to database properties –> Transaction Log-Shipping –> Script Configuration –> Script Configuration to New Query Window.
- Script opened in new query window will be in two parts with first part to be run at LS primary and second part to be run at LS secondary
–****** Begin: Script to be run at Primary: [SQLAG1] ******
<Script>
— ****** End: Script to be run at Primary: [SQLAG1] *****
–****** Begin: Script to be run at Secondary: [SQLDRInst] ******
<Script>
— ****** End: Script to be run at Secondary: [SQLDRInst] ***** - As LS has already been configured between primary replica SQLInst1 and SQLDRInst , now we need to configure LS and LS jobs on secondary replica SQLInst2 to complete the setup. For this, failover the availability group to SQLInst2 –> Connect to new primary replica using listener name SQLAG1–> Run the first part of script
This will do required configuration and create LS backup jobs on SQLInst2. - Configuration of LS for database involved in AG is complete now and log shipping will continue to work irrespective of current primary replica.
Limitations: Although log shipping will continue to work fine, we need to keep below points in mind.
- For any changes in log shipping configuration such as changing the threshold value of backup/restore alert, backup retention period etc. , it need to be done on other replicas as well after failing over the AG to that replica.
- For removing the log shipping, you need to remove monitoring configuration from current primary replica by unchecking “Enable this as a primary database in a log shipping configuration” from database properties –> failover AG to another replica –> uncheck “Enable this as a primary database in a log shipping configuration” from database properties
- Please DO NOT disable LS backup job on secondary replica and DO NOT add any step in job to check primary replica to execute backup.
LS backup job completes successfully on secondary replica stating “This node is part of an availability group and not its preferred backup replica” and log_shipping_monitor_primary table gets populated with last_backup_date as last run of LS backup job and last_backup_file as “****This node is part of an availability group and not its preferred backup replica****”
If we disable LS backup job on secondary replicas, LS alert job will start failing on secondary and server level report “Transaction Log Shipping Status” will show in RED on secondary replica.
Hope this information will help you!
Happy learning!!