SQL Server AlwaysOn combined with Log Shipping (Automatic)
Introduction
It is more sensible to configure another secondary replica of AlwaysOn in secondary site to achieve DR with asynchronous mode but again there are reasons like DR site is already a part of another WSFC, infra limitation, etc.
Before continuing any further, let me clarify that it has been consider that audience has understanding of -
What is AlwaysOn and Log-Shipping?
How to setup it ? Pre-requisites and all.
You can refer below links :
Log-Shipping - https://msdn.microsoft.com/en-gb/library/ms190640.aspx?f=255&MSPPError=-2147217396
Setup
Consider the following setup:
Primary Site (DataCentr1)
- One Node named Server1 running SQL Server 2012 Instance named SQL1
- Another Node named Server2 running SQL Server 2012 instance named SQL2
- Enable and setup AlwaysOn between Server1 & Server2; Create an Availability Group say AG1 with Listeners AGL port-1147.
- DB1 has been added in the Availability Group AG1 running on SQL1 as Primary and SQL2 as Secondary replica.
Now we have a requirement to setup DR for this database for which we will be using Log-Shipping to send log backups at DR site
Disaster recovery Site (DataCentr2)
- Node Server3 running SQL Instance named SQL3.
Now we can setup Log-Shipping for DB1 database to SQL3 Instance running DR Site (DataCentr2).
Problem is that if DB1 database fail-over from SQL1 to SQL2 manual intervention is required to re-initialize Log-Shipping (Check these blogs)
Troubleshooting
Few days back I was given a similar setup and was asked to help with the situation. They had setup Log-Shipping between SQL1 and SQL3, but when DB1 is fail-over to SQL2 Log-Shipping breaks. Either they had to fail back DB1 back to SQL1 before next T-Log Backup happen or have to re-initialize it if LSN chain is broken.
Now what I did
Setup Log-Shipping on Database part of AlwaysOn
Currently AlwaysOn is already setup as per our consideration and DB1 is online on SQL1 as primary replica.
Note: Backup Preferences for Availability Group AG1 has been set as “Where should backups occur?
Answer: Primary
In SSMS, Connect Primary AlwaysOn Replica with Listener Name (AGL,1147) not with the SQL Instance Name (Server1\SQL1).
If we connect with Listener it will connect to the SQL Instance which is currently the Primary replica.
Now enable Log-Shipping for DB1 database
- Configure Backup Settings (Note: the Backup path\share should be accessible from both the SQL instance SQL1 & SQL2).
- Add Secondary Server & configure Secondary Database Settings (Copy Files, Restore Transaction Log as per your requirement).
We have setup the Log-Shipping between SQL1 and SQL3 using Listener not the SQL Instance Name. Verify the setup if it is working fine.
Open DB1 Properties-> Transaction Log-Shipping->Script Configuration-> Script Configuration to New Query window.
This will open Log-Shipping configuration script in a new query window. This script is divided in two parts:
- Script to be run at Primary Instance (SQL1)
- Script to be run at Secondary Instance (SQL3)
Now we have to enable Log-Shipping on Secondary Replica of AlwaysOn i.e. SQL2 so that when AG1 is fail-over to SQL2 Log-Shipping continues to work.
Fail-over Availability Group AG1 from SQL1 to SQL2. Done. SQL2 is now our Primary replica.
Again Connect Primary replica using Listener Name, this time it will connect to SQL2.
Now run the First part of the Log-Shipping script which we have scripted out earlier against SQL2 Instance.
-- ****** Begin: Script to be run at Primary: [SQL1] ******
-- ****** End: Script to be run at Primary: [SQL1] ******
It will configure and create backup job on SQL2 for DB1 database and enable DB1 to take part in Log-Shipping.
Now, actually it’s done.
Log-Shipping has been configured on both nodes. Now whether the AG1 is Primary on SQL1 or SQL2, Log-Shipping will continue to work between Primary replica and SQL3 without any manual intervention.
Nor we have to disable\enable any job after any AG1 fail-over between SQL1 & SQL2.
As we have setup Backup Preferences to Primary replica only, LsBackup job will skip the log backup on any Secondary replica.
Check status
We can use below scripts to check status of Log-Shipping on Primary and Secondary Database.
Run on Primary Database
select primary_server,primary_database,
DATEDIFF(mi,last_backup_date,getdate()) as time_since_last_backup,
last_backup_date,last_backup_file from [dbo].[log_shipping_monitor_primary]
Run on Secondary Database
select ls.primary_server,ls.primary_database,
DATEDIFF(mi,lms.last_restored_date,getdate()) as time_since_last_restore,
lms.last_copied_date,lms.last_copied_file,lms.last_restored_date,
lms.last_restored_file,
lsd.disconnect_users,ls.backup_source_directory,
ls.backup_destination_directory,ls.monitor_server
from
msdb.dbo.log_shipping_secondary ls
join
msdb.dbo.log_shipping_secondary_databases lsd
on lsd.secondary_id=ls.secondary_id
join
msdb.dbo.log_shipping_monitor_secondary lms
on lms.secondary_id=lsd.secondary_id
Credits
Special thanks to my colleague Laddoo.