Step by Step Configuring AlwaysOn with Log Shipping

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 


Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads