question

SriramM-7113 avatar image
0 Votes"
SriramM-7113 asked AmeliaGu-msft commented

Sql server cluster node failover details

Hi


I would like to know the SQL server cluster node failover details immediately as soon as the passive node is active as I have some dependency. For that, I see sys.dm_os_cluster_nodes view will provide the active/passive node details, but I am not able to write a trigger on this view to send a mail automatically.

I spent some time but was not able to find out. I can write a SQL job to monitor but I will not prefer that.

Could you please help in guiding me to get a notification mail once the active node becomes passive or vice versa.

Thank you.

Thanks & Regards,
Sriram

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image AmeliaGu-msft ·

Hi Sriram,

Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer. This would benefit the community, and encourage the community member to keep working on your issues.
Best Regards,
Amelia

0 Votes 0 ·

4 Answers

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered JeffreyWilliams-3310 published

Is this a standard FCI instance? If so - then you can create a SQL Server agent job that executes on start-up.

5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi Sriram,

Please refer to the scripts in the following articles which might help:

Automatic Alert Generation in the event of SQL Server Cluster Failover
How to monitor SQL Server failover events automatically
SQL Server Restart or Cluster Failover Notification

Best Regards,
Amelia


5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SreekanthMadambath-0012 avatar image
0 Votes"
SreekanthMadambath-0012 answered

Create an SQL Server alert with cluster failover event id.

5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Soccan avatar image
0 Votes"
Soccan answered

Or if you want to go more windows and create a scheduled task that has a triger for event id 1201 in the windowsfailoverclustering/operational log. We have a misbehaving apppool in IIS that we restart this way.... So while your at it, maybe you could get this task to handle your dependency?

5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.