How monitor SQL Cluster

Step 1:
Create a table named [CLUSTERFAILOVERMONITOR] in the master database of the SQL Server instance, with a column [PREVIOUS_ACTIVE_NODE]. Use the query below for creating the table.

create table CLUSTERFAILOVERMONITOR ( PREVIOUS_ACTIVE_NODE varchar(30) )

Step 2:

Insert the value of the currently active node in that table. Use the query below for this.

insert into CLUSTERFAILOVERMONITOR
values
(
'Node1_Name'
)
Step 3:
Create a job with code shown below in it. The job will execute on the master database, will run every minute (can be run every 5-10 seconds in SQL Server 2008) and when the failover happens it will send a notification.

After the failover has occurred, this job will change the value of PREVIOUS_ACTIVE_NODE column in the CLUSTERFAILOVERMONITOR table that we created in step1, and is therefore ready to monitor the next failover event.

Declare @var1 varchar(30) SELECT @var1= PREVIOUS_ACTIVE_NODE FROM CLUSTERFAILOVERMONITOR CREATE TABLE PHYSICALHOSTNAME ( VALUE VARCHAR(30), CURRENT_ACTIVE_NODE VARCHAR(30) ) INSERT INTO PHYSICALHOSTNAME exec master..xp_regread 'HKEY_LOCAL_Machine', 'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\', 'ComputerName' declare @var2 varchar(30) SELECT @VAR2=CURRENT_ACTIVE_NODE FROM PHYSICALHOSTNAME if @VAR1<>@VAR2 Begin EXEC msdb..sp_send_dbmail @profile_name='DBAMail', @recipients='xyz@company.com', @subject=' Failover occurrence notification - SQLExample', @body='Cluster failover has occured for instance SQLExample. Below given are the previous and current active nodes.', @QUERY='SET NOCOUNT ON;SELECT PREVIOUS_ACTIVE_NODE FROM CLUSTERFAILOVERMONITOR;SELECT CURRENT_ACTIVE_NODE FROM PHYSICALHOSTNAME;SET NOCOUNT oFF' update CLUSTERFAILOVERMONITOR set PREVIOUS_ACTIVE_NODE=@VAR2 End DROP TABLE PHYSICALHOSTNAME