As a SQL Server consultant, I often come across various challenges and issues faced by clients. Recently, I encountered a situation where a client had successfully deployed log-shipping as their disaster recovery solution, but they were unable to complete the project sign-off due to an issue with the log-shipping monitoring. In this blog post, I will share the steps I took to fix the error “There was an error configuring the remote monitor server.”
Upon investigating the issue, I found that all the log-shipping related jobs (backup, copy, and restore) were running successfully. However, the transaction log status report was not getting updated and was displaying incorrect information. Further analysis revealed that the system tables were not being updated, which was causing the monitoring to fail.
The solution to this problem was not related to the log shipping itself, but rather to the connection with the monitor server. The monitoring process relies on a linked server connection to the monitoring server on both the primary and secondary instances. In order to establish this connection correctly, I followed these steps:
- Open SQL Server Management Studio (SSMS) and navigate to the Server Objects section.
- Expand the Linked Servers folder and locate the linked server created by log-shipping. It will have a name starting with “LOGSHIPLINK_<Monitor server Name>_-<ID>”.
- Right-click on the linked server and select Properties.
- In the Properties window, go to the Security tab.
- Change the radio button for “Login to be made using this security context”.
- Provide the SA username and password for the monitor server.
- Click OK to save the changes.
After making these changes, the issue with the remote monitor server was resolved. The log-shipping monitoring started working correctly, and the transaction log status report was updated as expected.
It is worth noting that according to the SQL Server documentation, only members of the sysadmin fixed server role can run the stored procedure “sp_refresh_log_shipping_monitor” that is used for manual refresh. In this case, the client already had the necessary permissions, so the issue was not related to permissions.
As a SQL Server consultant, encountering and resolving such issues is a valuable learning experience. It allows me to gain insights into real-world implementations and challenges faced by customers, which in turn helps me provide better solutions and share my knowledge through blog posts like this one.
Thank you for reading! If you have any questions or need further assistance, feel free to leave a comment below.