Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

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.

image

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.

image

</EDIT 13-April-2017>

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    7 Responses to “AlwaysOn : Better Together – Using AlwaysOn Availability Group and Log Shipping”

    1. Jayesh Shetty said

      Hi Balmukund,

      Can you please provide the Detail Deployment for above ALWAYSON and Logshipping Configuration.

      Regards
      Jayesh Shetty

    2. Chad said

      Was the Step by Step guide ever completed?

    3. Will this solution work in case if we have to configure reverse log shipping between DR (make this DC) and AON nodes (standby-DR)?

    4. 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.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: