Tuesday, 4 April 2017

Step by Step Configuring AlwaysOn with Log Shipping

blogger tricks
One of my clients came up with a requirement to use log shipping along with an AlwaysOn Availability Group. They cannot extend an AlwaysOn replica to the disaster recovery site for a few reasons listed below;
  • Infrastructure or staffing to maintain WSFC configurations between different sites
  • Delayed Recovery – In Log-shipping we can have definite delay on secondary database, SLAs on RPO, RTO force a fast recovery from manual error which only can be realized with delayed recovery restoring the transaction log backups on one instance of the HA/DR strategy
We have two SQL Servers in AlwaysOn AG Group SQL1 and SQL2 in Primary site for HA. One SQL Server instance SQL3 in secondary site for disaster recovery. The primary site is already setup with a two node AlwaysOn Availability Group (AG) with SQL1 as the primary replica and SQL2 as the secondary replica. 
In the below screenshot, SQL1 is the primary replica and SQL2 is the secondary replica

To configure AlwaysOn Availability Groups, Reference: http://mssqllover.blogspot.in/2017/02/alwayson-availability-groups-step-by.html

How to setup Log-Shipping on Database part of AlwaysOn Availability Groups

Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers. By default, Alwayson backup preferences are set to prefer the secondary. You have to change backup preference and select as primary. In an AlwaysOn Availability Group, BACKUP LOG supports regular log backups only. COPY_ONLY transaction log backups are not supported on secondary replicas.
Right click on the AG group and select backup preference. Select the primary as the backup preference.


Read My Complete Article "Here"





Ganapathi varma Chekuri
Lead SQL DBA, MCP
Email: gana20m@gmail.com

Linkedin 




3 comments:

  1. Could this work with Read-Only replicas? If we have enabled secondary replicas for ApplicationIntent = Readonly, you're supposed to be able to run backups from the read only replicas. So I wonder if in that case these read only replicas could work for log shipping. Thanks.

    ReplyDelete
  2. Hi Eliforp,

    No it doesn't work on read-only replicas. You have to change backup preference and select as primary. In an AlwaysOn Availability Group, BACKUP LOG supports regular log backups only. COPY_ONLY transaction log backups are not supported on secondary replicas.

    Thanks,
    Ganapathi varma

    ReplyDelete
  3. Hi Ganapathi,

    I have a transaction log backup scheduled for every 2 hours. When i create the log shipping, will it create its own transaction log for every 15 min and apply on the secondary server? In that case, will it truncate transaction log after every backup ?

    Regards
    Rajan

    ReplyDelete