December 1, 2008 at 8:45 am
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 = (SELECTdata 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
October 13, 2011 at 9:30 pm
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
February 5, 2013 at 6:10 pm
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
Modified6/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/2012Amanda 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.
February 6, 2013 at 1:41 am
An easy option may be to set a job to run at the time SQL Agent starts up which sends a mail.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply