AlwaysOn : Better Together – Using AlwaysOn Availability Group and Log Shipping
Posted by blakhani on July 3, 2014
In recent past once of my friend came with a requirement where she wanted to use log shipping along with AlwaysOn Availability Group. I asked “are you not happy with 4 secondary replicas in SQL 2012 and 8 secondary replicas in SQL 2014? Do you want more?” and she said that it’s not because of number of secondary replica, its about controlling the data movement and restore delay on secondary database in log-shipping. This made me think broader and here is list of reasons why someone would use AlwaysOn Availability Group and Log Shipping together.
- Delayed Recovery – In Log-shipping we can have definite delay on secondary database. This would safe guard DBA from “oops!” and “was that the production server?” situations. Log shipping can control the delay of transaction log restore while Asynchronous secondary replica can not.
- Single DR Server – Single Server can be used as multiple log-shipping pair’s destination.
- Infrastructure – Server at DR site can’t be a part of current cluster due to infrastructure limitations.
- Technical – Secondary server is already a part of different windows failover cluster. In availability group, we can’t have overlap of nodes by two windows clusters.
Keeping all of the above in mind, someone might want to use AlwaysOn Availability Group and Log Shipping together.
Here is a typical topology which can be deployed.
Number of replicas can be based on your own environment. Few important points to note:
- We need to configure both servers in availability group as primary for log shipping with same destination. This can be done by script or UI, based on you choice.
- Log Shipping knows about backup preference and backup priority for availability group. This means that we can offload backups on secondary.
- Backup should be taken to shared location so that the copy job is always using same location to pick the files even after failover/role change of availability group.
In my next blog, I would show step by step deployment guide of this configuration.
<EDIT 13-April-2017>
Today, I was trying to follow my own blog and was having trouble. Here are the high level steps.
- Configure Log-shipping from Primary replica in AlwaysOn to LogShipping secondary server. No special step needed here.
- On Secondary replica, enable only the backups. Don’t add secondary.
</EDIT 13-April-2017>
Help: SQL Server 
Jayesh Shetty said
Hi Balmukund,
Can you please provide the Detail Deployment for above ALWAYSON and Logshipping Configuration.
Regards
Jayesh Shetty
Chad said
Was the Step by Step guide ever completed?
blakhani said
No. Its still pending.
Ganapathi varma said
Will this solution work in case if we have to configure reverse log shipping between DR (make this DC) and AON nodes (standby-DR)?
Anil said
Hi Balmukund,
Could you please provide clarification on two points ?
1. “We need to configure both servers in availability group as primary for log shipping with same destination. This can be done by script or UI, based on you choice” – Lets say AG set up involves Server A and Server B. In given case database(s) can be online only in one server and to set up Primary server for log shipping we need to have Database should be in ONLINE state so we don’t be able to set up Primary server of Log Shipping on Secondary replica. Also how single database can be secondary for Two or more primary servers.
2. If AG has been set up in async mode and Primary server goes down – there won’t be automatic fail over and DBA has to manually do the fail-over and accept data loss.Please correct me if I misunderstood something.
Appreciate your help. Thank you.
Br,
Anil
blakhani said
1. I don’t think it would block you to setup log-shipping if its secondary replica. Have you tried it?
2. It has to be manual failover.
Anil said
No Sir, I couldn’t try. Just had put the DB in restoring mode then tried taking log back up and enabling log shipping which didn’t work. As AG supports offloading backup to secondary, it should work in that case.
Really appreciate your response and help. Thank you.