Run a query when the cluster node fails from one node to the other

Question

Post reply

Run a query when the cluster node fails from one node to the other

sunny.tjk

SSCertifiable

Points: 5041
More actions
June 20, 2016 at 3:13 pm

#400306

I'd like to run a query when the cluster node fails from node one node to the other.
Can someone help me achieve this?
I somehow feel looking through the errorlog is the only solution.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rvsc48 SSCertifiable Points: 7685 More actions · Answer 1

Are you looking for a way to be notified should a failover occur, like from e-mail?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

sunny.tjk (6/20/2016)
I'd like to run a query when the cluster node fails from node one node to the other.
Can someone help me achieve this?
I somehow feel looking through the errorlog is the only solution.

IIRC, the following will display the currently used node name.

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS');

You could create a job that runs every X number of minutes that reads what should be the current node name from a single row table that you've created. Could be multi-row as a kind of audit log for failovers, if you need such a thing.

If the failover occurs, the names won't match and you could then conditionally send the "I've flopped over to somenodenamehere" and then write that same node name back to your single row table to stop the notifications and setup for the next failover (possibly back to the original).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rvsc48 SSCertifiable Points: 7685 More actions · Answer 3

You could put this script in a Sql job pointing to master, assuming you have DB Mail setup already and a profile name (insert below), and, when you schedule it choose "Start automatically when SQL Server Agent starts" so it runs all the time and will email an alert should a failover occur on the instance -

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 ='YOUREMAILADDRESS'

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 ='YOURPROFILENAME',

@recipients=@recipientsList,

@subject = @subject ,

@body = @body,

@body_format = 'HTML' ,

@importance = 'HIGH'

Michael L John One Orange Chip Points: 27055 More actions · Answer 4

Create a job that runs at startup of SQL Server.

Put the following, after changing the values specific to you, in the job.

DECLARE @ServerName AS NVARCHAR(200) = 'Possible Failover of ' + CONVERT(nvarchar(200), @@SERVERNAME) + ' on server: ' + CONVERT(nvarchar(200), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'));

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'YourEmail@YourDomain',

@body = 'This is an informational message only: SQL services possibly restarted on the above SQL Server Instance.',

@subject = @ServerName;

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

sunny.tjk (6/20/2016)
I'd like to run a query when the cluster node fails from node one node to the other.
Can someone help me achieve this?
I somehow feel looking through the errorlog is the only solution.

Via TSQL

SELECT SERVERPROPERTY('ComputerNamePhysicalNETBios')

Via Powershell

Get-ClusterGroup "yourclustergroupname" | ft

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉