DBA Posts - How to monitor SQL Server Cluster failover as well as current active node?

Cluster Failover Notification Process

This process explains step by step process of monitoring cluster failover of SQL Server.

Basic Idea behind this process is to find active node (where sql server resources are running currently)

High Level steps:

1- create a table to keep track of current active node

2- create a sql agent job that will read data from above table

3- send an email notification as soon as the value in tracking table changes

Detail Process Steps:

create table ActiveNodeTracker

(
PriorActiveNode varchar(50)
)

insert into ActiveNodeTracker

values('ActiveNodeName')

Declare @Nodevar1 varchar(50)

SELECT @Nodevar1= PriorActiveNode FROM ActiveNodeTracker

CREATE TABLE HOSTNAME
(
VALUE VARCHAR(50),
PresentActiveNode VARCHAR(50)

)

INSERT INTO HOSTNAME
EXEC master..xp_regread 'HKEY_LOCAL_Machine',

'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\','PhysicalName'

declare @nodevar2 varchar(50)
SELECT @Nodevar2=PresentActiveNode FROM HOSTNAME

IF @Nodevar1<>@Nodevar2
Begin
EXEC msdb..sp_send_dbmail @profile_name='DBAdmin',

@recipients='email@orgnization.com',--Please replace with your org domain emails

@subject='Failover Alert',
@body='Cluster Failover Notification, please see Prior and present Active Nodes',
@QUERY='SET NOCOUNT ON;
SELECT PriorActiveNode FROM ActiveNodeTracker;
SELECT PresentActiveNode FROM HOSTNAME;
SET NOCOUNT OFF'
UPDATE ActiveNodeTracker SET PriorActiveNode=@nodevar2
END
DROP TABLE HOSTNAME