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?