We use logshipping as a Disaster Recovery for our production databases to a server in different location. We would need to monitor the synchronisation status of the logshipping secondary databases. We did not like to use the default monitoring job (LSAlert) created by logshipping and hence built our custom solution. Below are 2 scripts:

1. For monitoring logshipping which sends an alert only if the secondary database is 60min behind production.

2. This is more of having a history of logshipping monitoring when they are behind the threshold limit. For this to work, you need a user table created in your DBA maintenance database. This script will send logshipping report if secondary database is behind threshold limit and also checks for other databases which were out of sync in the last 6 hours (might have recovered by the time it sends an email but you might want to know it happened).

Script-1:


/*Create temporary table to hold the values */
IF OBJECT_ID('TEMPDB.dbo.#Logshipping_Monitor') IS NOT NULL
	DROP TABLE #Logshipping_Monitor

CREATE TABLE #Logshipping_Monitor
(	Primary_Server		nvarchar(100),
	Primary_Database	nvarchar(100),
	Secondary_Server	nvarchar(100),
	Secondary_Database	nvarchar(100),
	Restore_Latency		int,
	Min_Behind_Primary	int
)

/* Insert temp table with values */
INSERT INTO #Logshipping_Monitor
SELECT	secondary_server, secondary_database, primary_server, primary_database, 
		last_restored_latency,  DATEDIFF(minute, last_restored_date_utc, GETUTCDATE()) + 
                last_restored_latency [Minutes Behind Current Time]
FROM	msdb.dbo.log_shipping_monitor_secondary 
ORDER BY [Minutes Behind Current Time] desc

/*Send email alert only if Secondary is behind Primary by 60min */

--Set the body of the email 
DECLARE @xml nvarchar(max)
DECLARE @body nvarchar(Max)

SET @xml = CAST((SELECT	[Primary_Server] AS 'td','', [Primary_Database] AS 'td','', 
                        [Secondary_Server] AS 'td','', [Secondary_Database] AS 'td','', 
		        [Restore_Latency] AS 'td','',  
		        Min_Behind_Primary  AS 'td'
                 FROM	#Logshipping_Monitor
                 WHERE	Min_Behind_Primary > 60
                 ORDER BY [Min_Behind_Primary] Desc 
                 FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))

SET @body = '<html><body><H3>Logshipping Report</H3>
			<table border = 1>
			<tr>
			<th>Primary_Server</th>
			<th>Primary_Database</th>
			<th>Secondary_Server</th>
			<th>Secondary_Database</th>
			<th>Latency_Min</th>
			<th>Min_Behind_Primary</th>'
SET @body = @body + @xml +'</table></body></html>'
 
 --Send email
	EXEC msdb.dbo.sp_send_dbmail
	@profile_name = '<DatabaseMailProfile>',
	@Subject = 'Logshipping Monitoring',
	@recipients = '<Recipient Emails>',
	@body = @body,
	@body_format = 'HTML'

DROP TABLE #Logshipping_Monitor

 

Script – 2:

I had setup a table in my DBA database by the name DBA.dbo.Logshipping_Status and modified the above script to have the values entered into my DBA table. Below script will send the logshipping status in tabular format:


/***** Logshipping Monitoring*****/

-- =============================================
-- Author:      HariKumar Mindi
-- Description:	Setup a table in DBA database to insert logshipping monitoring values hourly 
--		This script will check if there are any secondary databases which are more than 60min
--              behind primary. If yes it would send email in tabular format for those that are more 
--              than 60min and also for other databases which were out of sync during last 6 hours
-- =============================================

DECLARE @min int
SET @min = (SELECT MAX(Minutes_Behind_Prod) FROM DBA.dbo.Logshipping_Status 
	    WHERE  Minutes_Behind_Prod > 50 
                   AND DATEDIFF(MINUTE, Current_DateTime, GETDATE()) <= 800) 

IF @min IS NOT NULL 
BEGIN 
DECLARE @xml1 nvarchar(max) 
DECLARE @xml2 nvarchar(max) 
DECLARE @body1 nvarchar(max) 
DECLARE @body2 nvarchar(max) 
SET @xml1 = 
CAST(( SELECT [Secondary Server] AS 'td','', [Secondary_Database] as 'td','', 
              [Primary_Server] AS 'td','', [Latency(Mins)] AS 'td','', 
              [Minutes_Behind_Prod] AS 'td','', [Current_DateTime] AS 'td' 
       FROM   DBA.dbo.Logshipping_Status 
       WHERE [Minutes_Behind_Prod] > 50 
             AND DATEDIFF(MINUTE, [Current_DateTime], GETDATE()) <= 60 
       FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX)) 

SET @xml2 = 
CAST(( SELECT [Secondary Server] AS 'td','', [Secondary_Database] as 'td','', 
              [Primary_Server] AS 'td','', [Latency(Mins)] AS 'td','', 
              [Minutes_Behind_Prod] AS 'td','',[Current_DateTime] AS 'td' 
       FROM   DBA.dbo.Logshipping_Status 
       WHERE  [Minutes_Behind_Prod] > 50 
              AND DATEDIFF(MINUTE, [Current_DateTime], GETDATE()) <= 360 
       FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))

SET @body1 = '<html><body><H3>Logshipping Out of Sync in Last 1 hour</H3>
			 <table border = 1>
			 <tr>
			 <th>Secondary Server</th>
                         <th>Secondary Database</th>
                         <th>Primary Server</th>
                         <th>Latency_Min</th>
                         <th>Minutes Behind Prod</th>
                         <th>Last Updated</th>'
SET @body1 = @body1 + @xml1 +'</table></body>'
SET @body2 = ''<html><body><H3>Logshipping Out of Sync in Last 6 hour</H3> 
                           <table border = 1> 
                           <tr> 
                           <th>Secondary Server</th> 
                           <th>Secondary Database</th> 
                           <th>Primary Server</th> 
                           <th>Latency_Min</th> 
                           <th>Minutes Behind Prod</th> 
                           <th>Last Updated</th>' 
SET @body1 = @body1 + @xml1 +'</table></body>' 
SET @body2 = @body1 + @body2 + @xml2 + '</table></body></html>' 

--Send email 

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = '<Database Mail Profile>', 
@Subject = 'Logshipping Monitoring', 
@recipients = '<Recipient Emails>', 
@body = @body2, @body_format = 'HTML' 
END

The above scripts helped me monitor logshipping status in DR setup and also during production SQL Server migration from one server to another.

If you have something to say on this post, leave a comment !

-Hari Mindi


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.