Blog Post

Windows Failover Cluster Monitor

,

Windows Failover Clusters are fantastic, they provide High Availability for mission critical SQL Server instances and make my life as a DBA better in so many ways.  Can they though, sometimes be too good?  I have had SQL Server instances failover between cluster nodes in the past that none of our alerting has picked up on!  I've only noticed the failover by stumbling across it days or even weeks later.  One way to prevent these "Ghost" failovers going unnoticed for prolonged periods of time that I use is to have a startup procedure on the instance to email an operator when a failover occurs.

Below is the script to create a startup procedure to achieve this.  You will need to update the @profile_name = 'SQLErrors' and @recipients = 'Chris@SQLServer365.co.uk' accordingly

I have played about with the WAITFOR DELAY a bit and found that 15 seconds is sufficient after the SQL Server Service has started and executed the startup procedure for the database mail engine to be ready and successfully send the email.

-- Set database context
USE master;
GO
-- Check if procedure exists
IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   [object_id] =OBJECT_ID('dbo.spEmailSQLServerRestart')
                    AND[type] IN ( 'P' ) )
      -- Drop procedure                    
    DROP PROCEDURE dbo.spEmailSQLServerRestart
GO
-- Create procedure                   
CREATE PROCEDURE dbo.spEmailSQLServerRestart
AS
    BEGIN
            -- Declare Variables
        DECLARE@strServer VARCHAR(128) = CONVERT(VARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
        DECLARE@strMailSubject VARCHAR(128) = 'SQL Server '
            + UPPER(@@SERVERNAME) + ' restarted!'
        DECLARE@strMailBody VARCHAR(1000) = 'SQL Server '
            + UPPER(@@SERVERNAME) + ' restarted at '
            + CONVERT(VARCHAR(12), GETDATE(), 108) + ' on '
            + CONVERT(VARCHAR(12), GETDATE(), 103)
            + ' Now running on server: ' +@strServer
            -- Wait for the database mail engine to start
        WAITFORDELAY '00:00:15'
            -- Send Email
        EXECmsdb.dbo.sp_send_dbmail @profile_name = 'SQLErrors',
            @recipients = 'Chris@SQLServer365.co.uk',
            @subject =@strMailSubject, @body =@strMailBody,
            @body_format = 'HTML';
    END      
GO
-- Set procedure as startup procedure   
EXEC sp_procoption @ProcName = 'spEmailSQLServerRestart',
    @OptionName ='STARTUP',@OptionValue = 'ON';

GO


Enjoy!

Chris

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating