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.