Monitor Log Shipping

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » Monitor Log Shipping

Monitor Log Shipping

Rate Topic

Display Mode

Topic Options

Author

Message

billh-508166

Posted Friday, January 11, 2013 7:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:43 AM
Points: 5, Visits: 22

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.

Post #1406024

Ignacio A. Salom Rangel

Posted Sunday, January 20, 2013 2:17 PM

Hall of Fame

Group: General Forum Members
Last Login: Friday, May 10, 2013 2:49 PM
Points: 3,051, Visits: 1,356

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

Post #1409308

Dhananjay-440114

Posted Tuesday, January 22, 2013 3:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 24, 2013 5:29 AM
Points: 114, Visits: 273

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

Post #1409906

billh-508166

Posted Tuesday, January 22, 2013 7:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:43 AM
Points: 5, Visits: 22

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.

Post #1410033

« Prev Topic | Next Topic »

Permissions