Failover Alerts

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Failover Alerts

Failover Alerts

Rate Topic

Display Mode

Topic Options

Author

Message

oraculum

Posted Monday, December 01, 2008 8:45 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 03, 2012 6:35 AM
Points: 97, Visits: 501

Hello everyone ... i have been searching and searching for a good way to email alert me when my sql cluster node fails over... now i am sure oneone will have something fantastic in place.. but i thought i would share my simple script that i run regularly to alert me whether a failover has occurred.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[USP_CheckClusterNode] Script Date: 12/01/2008 14:46:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/* */

Create Procedure [dbo].[USP_CheckClusterNode]

AS

-- query to test for failover--
-- runs every 5 mins to check failover---

-- Extract Physical Machine Name for Server--

create table ##cluster (Value varchar(100), Data varchar(100))

insert into ##cluster (Value, Data)

exec master..xp_regread 'HKEY_LOCAL_Machine',
'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\',
'ComputerName'

-- Extract PVirtual Machine Name for Server--

insert into ##cluster (Value, Data)
SELECT 'VirtualName' as Value, CONVERT(sysname, SERVERPROPERTY('servername')) as Data;

DECLARE @VIRTUAL VARCHAR(100)
DECLARE @PHYSICAL VARCHAR(100)
DECLARE @subject varchar(300)
DECLARE @Message varchar(4000)

SET @VIRTUAL = (SELECT data from ##cluster where Value = 'VirtualName')
SET @PHYSICAL = (SELECT data from ##cluster where Value = 'ComputerName')

print @virtual
print @physical

IF @virtual = 'YourVirtualServerName' AND @physical = 'YourPhysicalNodeName'
BEGIN
print 'Correct Node'
/*
set @message = 'This is a status check of the current active SQL Server Node' +CHAR(10) +CHAR(10) +'The Active node for ' +@VIRTUAL +' is : ' +@physical
set @subject = 'SQL SERVER FAILOVER STATUS: CORRECT ACTIVE NODE ' +@physical

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'emailaddress',

@body = @message,
@subject = @subject

drop table ##cluster
*/
END

ELSE
BEGIN
print 'Failover Occurred'

set @message = 'This is a status check of the current active SQL Server Node' +CHAR(10) +CHAR(10) +'The Active node for ' +@VIRTUAL +' is : ' +@physical
set @subject = 'SQL SERVER FAILOVER: Incorrect Active Node = ' +@physical
EXEC msdb.dbo.sp_send_dbmail
--@recipients = 'emailaddress',

@body = @message,
@subject = @subject

drop table ##cluster

END

--select * from ##cluster

Oraculum

Post #611393

Sanjeev-231530

Posted Thursday, October 13, 2011 9:30 PM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 2, Visits: 77

One can always use a System Startup Procedure to find the node failover, instead of having the job run every 5-10 mins.

Please read this how it can be done: http://msdn.microsoft.com/en-us/library/ms191129.aspx

Post #1190288

Rottengeek

Posted Tuesday, February 05, 2013 6:10 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:50 PM
Points: 8, Visits: 189

Here is my script - it isn't perfect, i wrote it when i was frustrated and wanted to get it out right away, you'll probably want to change it depending on how your environment is set up:
CREATE PROCEDURE [Monitor].[ClusterFailover] @recipients nvarchar(max)= 'xyz'
AS

/*********************************************
runs when SQL Agent starts up and determines the node
on which SQL is running and alerts the dba team to failover
This way we know when a server fails over

Created: 5/7/2012
Created By: Amanda Bates
Modified 6/5/2012

****************************************/

Declare

@subject nvarchar(200),
@body nvarchar(max),
@query nvarchar(max),
@execute_query_database sysname,
@ClusterName nvarchar(50),
@ClusterNode1 nvarchar(50),
@CurrentNode nvarchar(50),
@ClusterNode2 nvarchar(50)

SELECT @ClusterName = @@Servername
SELECT @CurrentNode = CONVERT(nvarchar(50), ServerProperty('ComputerNamePhysicalNetBIOS') )

SELECT @ClusterNode1 = MIN(NodeName) FROM sys.dm_os_cluster_nodes
SELECT @CLusterNode2 = MAX(NodeName) FROM sys.dm_os_cluster_nodes

SELECT @subject = @ClusterName + ' Failover Or Restart!'

SELECT @body = @ClusterName + ' ' + 'is now running on node '+ @CurrentNode + ' Nodes: '+ @ClusterNode1 + '/' + @ClusterNode2

EXEC msdb.dbo.sp_send_dbmail
@recipients= @recipients,
@subject = @Subject,
@body = @body

GO

and then you do this:

ALTER PROCEDURE [dbo].[Startup_procs] AS

/************************************
this stored procedure runs when the server starts, and executes
the below stored procedure.
Created: 5/1/2012 Amanda Bates
************************************/

EXEC Monitor.ClusterFailover
----this sends an email indicating the server is coming back up after
----an outage. it will also include the name of the node it is on
----email addresses are hard-coded into the sub-proc

GO

EXEC sp_procoption N'[dbo].[Startup_procs]', 'startup', '1'

GO

I was lazy or i would have figured out a way to make the email addresses dynamic.

Post #1416192

anthony.green

Posted Wednesday, February 06, 2013 1:41 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

An easy option may be to set a job to run at the time SQL Agent starts up which sends a mail.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1416308

« Prev Topic | Next Topic »

Permissions