Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Failover Alerts Expand / Collapse
Author
Message
Posted Monday, December 1, 2008 8:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 3, 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
Posted Thursday, October 13, 2011 9:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:21 PM
Points: 4, Visits: 115
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
Posted Tuesday, February 5, 2013 6:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:04 AM
Points: 8, Visits: 212
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
Posted Wednesday, February 6, 2013 1:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 5,221, Visits: 5,116
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 »

Add to briefcase

Permissions Expand / Collapse