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'
AND
counter_name = 'Log
Send Queue KB'
AND
Instance_name != '_Total'
AND
Cntr_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'
AND
counter_name = 'Log
Send Queue KB'
AND
Instance_name != '_Total'
AND
Cntr_Value > @Threshold
FOR
XML
PATH('tr') ,
TYPE
)
AS NVARCHAR(MAX)) + N'</table>';
-- Email results
EXEC
msdb.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'
AND
counter_name = 'Redo
Queue KB'
AND
Instance_name != '_Total'
AND
Cntr_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'
AND
counter_name = 'Redo
Queue KB'
AND
Instance_name != '_Total'
AND
Cntr_Value > @Threshold
FOR
XML
PATH('tr') ,
TYPE
)
AS NVARCHAR(MAX)) + N'</table>';
-- Email results
EXEC
msdb.dbo.sp_send_dbmail @profile_name
= @EmailProfile,
@recipients = @EmailRecipient, @subject = @EmailSubject,
@body =
@tableHTML, @body_format = 'HTML';
END
GO
Enjoy!
Chris
No comments:
Post a Comment