Wednesday 14 November 2012

Database Mirroring Performance Counters


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