Share via

Sql server cluster node failover details

Abhinava Sriram Mothe 1 Reputation point
2020-08-18T18:30:52.59+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,345 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jeffrey Williams 1,891 Reputation points
    2020-08-18T21:45:44.97+00:00

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

    0 comments
  2. AmeliaGu-MSFT 13,971 Reputation points Microsoft Vendor
    2020-08-19T02:18:42.353+00:00
    0 comments
  3. Sreekanth Madambath 86 Reputation points
    2020-08-19T08:44:21.39+00:00

    Create an SQL Server alert with cluster failover event id.

    0 comments
  4. Soccan 16 Reputation points
    2020-08-20T08:31:57.66+00:00

    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?

    0 comments