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.

1. Configure Log-shipping from Primary replica in AlwaysOn to LogShipping secondary server. No special step needed here.
2. On Secondary replica, enable only the backups. Don’t add secondary.

</EDIT 13-April-2017>