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
To configure Log-shipping, Reference: http://mssqllover.blogspot.in/2017/03/step-by-step-logshipping-configuration.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.
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.
ReplyDeleteHi Eliforp,
ReplyDeleteNo 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
Hi Ganapathi,
ReplyDeleteI 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
yes. it would truncate the log backup.
Deletethank you. please share and subscribe.
ReplyDelete