Monitor Log Shipping

Question

Monitor Log Shipping

billh-508166

SSC Enthusiast

Points: 150
More actions
January 11, 2013 at 7:30 am

#287712

Monitoring Log Shipping
My client in Manhattan maintains a disaster recovery server in South Brooklyn. We employ log shipping to sync five databases. I have been trying to determine the best way to programmatically determine whether the databases are synchronized, or perhaps better, when they were last synchronized.
The problem is complicated by the fact that the system appears healthy from the primary server’s perspective – it created the logs and shipped them successfully – but not from the secondary server’s perspective – sql agent shut down so logs were not restored and alerts not sent.
Does anyone have suggestions regarding “best practices” to monitor system that employ’s log shipping. We do have a monitor server available, but it is currently on the same file system as the primary. I would rather not have to lobby for a third server – monitoring from the secondary would be fine if it can accurately tell me that status of the secondary in the event that the primary goes missing.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 1

You can use the log shipping reports to monitor the state of the log shipping. These reports are standard. You can find more info on the link below:

http://msdn.microsoft.com/en-us/library/ms181149.aspx

My blog

Dhananjay-440114 Ten Centuries Points: 1365 More actions · Answer 2

All other ways will be just wastage if your sql agent services getting shutdown abruptly. Its good to find out reason for that and fixes that will be probably the best solution. Why don't you change the services parameter to start automatically when it stops.

But still you can try the other way around:- Query the msdb.log_shipping_secondary_databases and related table from other server (May be Primary) using link server and send alert.

Regards

Dhananjay

billh-508166 SSC Enthusiast Points: 150 More actions · Answer 3

Thanks for the response - confirms that we did not miss something obvious - checking the tables on the monitor server seems to be the best approach. We check SQL Agent from the file system already - easy enough to add log shipping monitor to the same routine.