Implement Log Shipping with AlwaysOn Availability Group

Audience

This article is for those who understands bot AlwaysOn Availability Group and Log Shipping. I will not go into the details explaining the both technologies. The article will only cover the tricky part that is involved to implement Log Shipping with AlwaysOn Availability Group.

Scenario

Let’s suppose you have AlwaysOn Availability Group implemented. Assume you have more than one databases part of the availability group but due to some technical requirements, you need to provide a read only access of only one database. Here, Log Shipping can help you to replicate data on the another machine to give access to the data.

Environment

Let’s say we have one node as SQL1 and one node as SQL2. You need to apply Log Shipping on node SQL3 which is not the part of the availability group replicas. You need to first apply Availability group with Listener (IP and port). Make sure you can connect with the Primary node (say, its SQL1 is Primary and SQL2 is secondary at this time) using Listener IP and port that you specified at the time of the creation of Availability Group on Listener Tab.

Implement Log Shipping

Connect the primary Replica using Listener IP and port and implement Log Shipping using SQL3 as your Log Shipping node. Make sure that the shared backup path is accessible by all nodes SQL1, SQL2 and SQL3.

Tricky Part

Right click the availability group and go to Backup Preferences page. Here select, “Primary” as you backup preference.

Since the log shipping is applied to the Primary replica SQL1 and not on secondary replica SQL2, so when the failover will occur from SQL1 to SQL2, the log shipping will break. It is because the log shipping jobs are not applied on the secondary SQL2 node and in case of any log backup on SQL1, the LSN chain will break even when you will go back to SQL1 from SQL2. So the trick here is to create the script of the Log Shipping that you implemented on Primary node SQL1. The script is divided into 2 parts. Copy the “Primary” part of the script. Failover to the SQL2. Connect to SQL Server using the same Listener IP and port. This will connect you to SQL2. Open the query window and paste the copied script. Run the script. The script will create and configure the Log Shipping on SQL2 for you. Now you have log shipping is configured on both nodes SQL1 an SQL2.

Result

Now, in case of failover, your log shipping will never break as the backups will be taken on Primary node only and the LSN will be maintained.