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