1

I setup Log Shipping. I have Secondary Destination Server as Monitoring server also. For some reason when running this system stored procedure or looking at Server-->Reports-->Transaction Log Shipping Status: 

EXEC sp_help_log_shipping_monitor

Secondary Destination (also Monitoring): I only see rows for Secondary Destination, do not see any rows for Primary

Primary Server: I see rows for Primary, even though this is not the monitoring server,

My Monitor logins are temporarily SA on both servers, to make sure its not security issue, so not sure why is Primary is not showing on Secondary Monitoring server. Is this the way Log Shipping is supposed to work by functionally ?

Improve this question
1
  • Can you please add the output of report or the SP, let me see what it actually returns. This will helpful
    – Shanky
    Jun 24, 2018 at 6:59

1 Answer 1

Reset to default
1
+50

Have you configured your log shipping correctly?

I've reproduced the issue on SQL Server 2014. So to fix it, you need to setup the correct connection of your secondary/monitor instances using SERVERNAME\INSTANCENAME. Check this link.

ROOTCAUSE: When the setting up logshipping, for remote monitor server, we check the output of SELECT @@SERVERNAME with that of the name provided in remote server. So if the two names do not match, for example if you are using an alias or a port number or a tcp protocol, then logshipping status report will not show any configuration information. The report will not even depict the primary\secondary server or any files being backed up, copied or restored depending on whether the monitor is a remote monitor or local to Primary/Secondary.

This behavior is observed when the monitor server is a Remote SQL Instance and we are not connecting to it via servername\instance name which is also the output of SELECT @@SERVERNAME query executed on monitor server itself, but rather connecting via an alias or a protocol or a port number. This happens in SQL Server 2005 as the Primary/Secondary server instances use Linked Servers to connect to the Monitor Server instance.

Once you properly configure log shipping use the follow command below to check the report:

SELECT @@SERVERNAME;
GO
EXEC sp_help_log_shipping_monitor;
GO
Improve this answer
0

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.