Transaction Log Shipping Status Report for monitor server will not pull up information if alias is used for monitor server
While using Logshipping, if a servername for monitor is used which is other than the @@servername output, for example an alias, the transaction log shipping status report on the monitor server will NOT show any information about the primary and secondary server or any files copied or restored.
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.
Cause:-
=====
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.
Resolution:-
==========
If normal servername\instance is used instead of ALIAS\PROTOCOL\PORT No while setting up log shipping for the monitor server, the transaction log shipping reports would pull up correct information and even shows the latest file copied\restored.
Depinder Kaur
SE, Microsoft Sql Server
Reviewed By
Amit Banerjee & Rakesh Singh
Technical Lead, Microsoft Sql Server