This week’s SQL Skills insider email contains a simple but often too true scenario regarding database mirroring. If you haven’t signed up to the insider email I suggest you do as there is wealth of great information provided by the SQL Skills team on a Bi-Weekly basis.
In summary what good is a HA / DR strategy without monitoring it? Never assume once setup you are covered, because chances are when recovery is required you will fall foul of your neglect. In honesty I have seen this with other HA / DR techniques too and dare I say been guilty of this in the past myself. Below is the link to Paul’s blog which details the two counters and explains the importance of them for database mirroring sessions in particular when it comes to a failover;
Now there are many ways to skin a cat and I’m not saying this is the right way but it is a solution to the issue highlighted by Paul. I've created two scripts that can be used to email an operator if the counter in question exceeds a given threshold. You can use the scripts in a SQL Agent Job to periodically check these counters for added peace of mind. You can also change the threshold accordingly to fit what is deemed as acceptable in your environment.
/*
-----------------------------------------------------------------
Get all user tables for all online, read-writable user databases
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.co.uk
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- Set database context
USE msdb;
GO
-- Declare variables
DECLARE@EmailProfile VARCHAR(255)
DECLARE@EmailRecipient VARCHAR(255)
DECLARE@EmailSubject VARCHAR(255)
DECLARE @Threshold INT
-- Set variables
SET@EmailProfile = 'SQlServer365'
SET@EmailRecipient = 'Chris@SQLServer365.com'
SET@EmailSubject = 'Log Send Queue on ' + @@SERVERNAME + ' is greater than 1GB'
SET @Threshold = 1048576 -- 1GB
-- Check Log Send Queue KB
IF EXISTS ( SELECT 1
FROM sys.dm_os_performance_counters
WHERE [object_name] ='SQLSERVER:Database Mirroring'
ANDcounter_name = 'Log Send Queue KB'
ANDInstance_name != '_Total'
ANDCntr_Value > @Threshold )
BEGIN
DECLARE@tableHTML NVARCHAR(MAX);
SET@tableHTML = N'<style type="text/css">'
+ N'.h1 {font-family: Arial, verdana;font-size:16px;border:0px;background-color:white;} '
+ N'.h2 {font-family: Arial, verdana;font-size:12px;border:0px;background-color:white;} '
+ N'body {font-family: Arial, verdana;} '
+ N'table{font-size:12px; border-collapse:collapse;border:1px solid black; padding:3px;} '
+ N'td{background-color:#F1F1F1; border:1px solid black; padding:3px;} '
+ N'th{background-color:#99CCFF; border:1px solid black; padding:3px;}'
+ N'</style>' + N'<table border="1">' + N'<tr>'
+ N'<th>Database Name</th>' + N'<th>Last Backup Taken On</th>'
+ N'</tr>'
+ CAST(( SELECT td =Instance_Name ,
'' ,
td = cntr_value ,
''
FROM sys.dm_os_performance_counters
WHERE [object_name] ='SQLSERVER:Database Mirroring'
ANDcounter_name = 'Log Send Queue KB'
ANDInstance_name != '_Total'
ANDCntr_Value > @Threshold
FOR
XMLPATH('tr') ,
TYPE
)AS NVARCHAR(MAX)) + N'</table>';
-- Email results
EXECmsdb.dbo.sp_send_dbmail @profile_name = @EmailProfile,
@recipients = @EmailRecipient, @subject = @EmailSubject,
@body =@tableHTML, @body_format = 'HTML';
END
GO
/*
-----------------------------------------------------------------
Get all user tables for all online, read-writable user databases
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.com
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- Set database context
USE msdb;
GO
-- Declare variables
DECLARE@EmailProfile VARCHAR(255)
DECLARE@EmailRecipient VARCHAR(255)
DECLARE@EmailSubject VARCHAR(255)
DECLARE @Threshold INT
-- Set variables
SET@EmailProfile = 'SQlServer365'
SET@EmailRecipient = 'Chris@SQLServer365.com'
SET@EmailSubject = 'Redo Queue on ' + @@SERVERNAME+ ' is greater than 1GB'
SET @Threshold = 1048576 -- 1GB
-- Check Log Send Queue KB
IF EXISTS ( SELECT 1
FROM sys.dm_os_performance_counters
WHERE [object_name] ='SQLSERVER:Database Mirroring'
ANDcounter_name = 'Redo Queue KB'
ANDInstance_name != '_Total'
ANDCntr_Value > @Threshold )
BEGIN
DECLARE@tableHTML NVARCHAR(MAX);
SET@tableHTML = N'<style type="text/css">'
+ N'.h1 {font-family: Arial, verdana;font-size:16px;border:0px;background-color:white;} '
+ N'.h2 {font-family: Arial, verdana;font-size:12px;border:0px;background-color:white;} '
+ N'body {font-family: Arial, verdana;} '
+ N'table{font-size:12px; border-collapse:collapse;border:1px solid black; padding:3px;} '
+ N'td{background-color:#F1F1F1; border:1px solid black; padding:3px;} '
+ N'th{background-color:#99CCFF; border:1px solid black; padding:3px;}'
+ N'</style>' + N'<table border="1">' + N'<tr>'
+ N'<th>Database Name</th>' + N'<th>Last Backup Taken On</th>'
+ N'</tr>'
+ CAST(( SELECT td =Instance_Name ,
'' ,
td = cntr_value ,
''
FROM sys.dm_os_performance_counters
WHERE [object_name] ='SQLSERVER:Database Mirroring'
ANDcounter_name = 'Redo Queue KB'
ANDInstance_name != '_Total'
ANDCntr_Value > @Threshold
FOR
XMLPATH('tr') ,
TYPE
)AS NVARCHAR(MAX)) + N'</table>';
-- Email results
EXECmsdb.dbo.sp_send_dbmail @profile_name = @EmailProfile,
@recipients = @EmailRecipient, @subject = @EmailSubject,
@body =@tableHTML, @body_format = 'HTML';
END
GO
Enjoy!
Chris
