1

I would like to know the SQL Server cluster node fail over details immediately as soon as the passive node is active as I have some dependency on active node. 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 Server 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?

Thank you.

2 Answers 2

1

Just to add, your sql agent job could look like this, which works for us (this assumes you have database mail configured properly):

Set the schedule to be when sql server starts as described in Ronaldo's answer

DECLARE @importance AS VARCHAR(6)
DECLARE @body AS NVARCHAR(1000)
DECLARE @Subject  AS NVARCHAR(1000)
DECLARE @InstanceName AS VARCHAR(100)
DECLARE @NodeName AS NVARCHAR(100)
DECLARE @recipientsList VARCHAR(100)

SELECT  @recipientsList ='[email protected];[email protected]'
SELECT  @InstanceName =@@SERVERNAME
SELECT  @NodeName = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(200))
SELECT  @Subject  = 'Failover occured for SQL Server Instance '+@InstanceName
SELECT  @body = 'Failover occured for SQL Server Instance '+@InstanceName + 'This instance is currently running on the node '+@NodeName
SELECT  @importance ='High' 
EXEC msdb.dbo.sp_send_dbmail 
     @profile_name ='Dbas',
     @recipients=@recipientsList,
     @subject = @subject ,
     @body = @body,
     @body_format = 'HTML' ,
     @importance = 'HIGH'
0

The CREATE TRIGGER doc states that you can only reference a view by an INSTEAD OF trigger:

table | view
The table or view on which the DML trigger runs. This table or view is sometimes referred to as the trigger table or trigger view. Specifying the fully qualified name of the table or view is optional. You can only reference a view by an INSTEAD OF trigger. You can't define DML triggers on local or global temporary tables.

And here's the INSTEAD OF definition:

INSTEAD OF
Specifies that the DML trigger launches instead of the triggering SQL statement, thus, overriding the actions of the triggering statements.

I assume if you would use an INSTEAD OF trigger on a system view, you could break something.

I believe a job would be the best option if you would choose it to start automatically when SQL Server Agent starts. This way you won't need to schedule it to run frequently to see if there was any change. It would only run when your SQL Server started (which happens during a fail over). Here's the option you should choose in your schedule:

Job Schedule

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.