Sql-server – Configuring log shipping with one primary and two secondary instances

log-shippingsql server

I have three SQL Server instances (SQLInstance-1, SQLInstance-2, and SQLInstance-3).I want configure three (or more) servers:

  • SQLServer-1 – primary
  • SQLServer-2 – secondary
  • SQLServer-3 – secondary

I want the database in standby mode on the secondary servers – database available for querying, and users can access it, but in read-only mode.

I am able to configure this for two servers (one primary and one secondary), but when I try three servers, log shipping setup and secondary server (SQLInstance-3) gets stuck in restore mode, and the log shipping SQL Agent jobs on the second secondary (SQLInstance-3) fail repeatedly. I am confused here what to do?

The second secondary (SQLInstance-3) is showing No Last_Restore_file. I checked Transactional Log Shipping status and it has the same status for the restore file.

Does this mean log shipping is not configured properly between SQL Instance 1 & 3?

I set up log shipping originally following the process outlined in Configure Log Shipping (SQL Server). I didn't use the wizard, I restored the log backups on secondary, with SQL Agent jobs created for log restores. The copy and restore jobs failed.

I have checked Restoring Job failed with error 'Could not find a log backup file that could be applied' on Log Shipping in SQL Server (Secondary)? but it won't work for me. As suggested, I started from scratch and re-initialized the log shipping on secondary (2), jobs on secondary (1) failed with an error, and jobs on secondary (2) are running fine. Both secondaries are set up the same way, and I got successful notifications for log shipping, but jobs failed on one secondary.

How should I setup log shipping with one primary and multiple secondary servers?

Best Answer

Below are the steps that you need to follow :

  1. Take full backup on primary (server 1 ).
  2. Restore full backup on server 2 and 3 using no_recovery option.
  3. Take log backup on server 1
  4. Restore log backup on server 2 and 3 using no_recovery option.
  5. Using GUI wizard, add secondary servers (server 2 and 3).

This will help you set up tertiary logshipping. I have done it and it just works fine.

Note: When you failover, you need to be careful as to what server you are failing over and have to manually restore the tail log backup on the other secondary server.

Related Question