1

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?

Improve this question
1
  • What's the exact failure on the Copy/Restore steps.
    – Kris Gruttemeyer
    Jun 26, 2017 at 14:52

1 Answer 1

Reset to default
1

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.

Improve this answer
1
  • Thanks for inputs. I already tried for set up Log shipping as same you mentioned in above, it got successfully config Log-shipping between 1 primary and 2 secondary server. But Copy and Restore jobs on one of Secondary servers failing frequently after setup (2nd Secondary server working fine and Jobs are running with out error). I double check setup for both secondary everything was looking good and I haven't figure it out what is the exact issue causing Job failures?
    – CR241
    Jul 11, 2017 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.