How to get notification when one node fails to another?

Question

How to get notification when one node fails to another?

Vidas Zabinskas

Hall of Fame

Points: 3258
More actions
February 22, 2005 at 1:36 am

#164607

The problem looks simple but how to implement this? We have multinode cluster and would like to get notifications when the problem occurs and one node failover to another. Maybe anyone has any ideas or links regarding to that?
Wnidows 2003 server; SQL Server 2000 ENT
Thanks in advance

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

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

beath SSCrazy Points: 2939 More actions · Answer 1

The failover messages are written to the event log so anything that can report on that would do the job for you. It depends how much you want to spend and what level of notification you want.

We use HP Openview to monitor the event logs and then use Alarmpoint to send notifications via email and/or phone.

cheers

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 2

Thank you for your reply

I'm not fastidious and our cluster doesn't perform criticial services, so there could be the simplest way to get the notification about failover to email. There are a lot of different 3rd part tools on internet but I don't need all of these services which they offer. To download free evaluation version for some period... well I guess it's not the best solution too.

What I need is only to force the notification about the failover to come to my email. Maybe there are some tools in SQL server to solve this problem? For ex. job sends email to the SQL server operator when it founds particular event in the event log?

Or maybe I want to much?

sa24 SSCrazy Eights Points: 8027 More actions · Answer 3

The code below is nothing fancy but you can schedule something simple as below. You can replace $INST1 with your Service name and you can replace echo "hello" with some command which can send you an email. You can then schedule the same to run every 5 mins. or so.

set ANODESTATE=0

srvinfo \\servername | find /c "$INST1" > test.txt

type test.txt | find "1" > nul

set ANODESTATE=%ERRORLEVEL%

if %ANODESTATE% NEQ 0 echo "hello"

John Jakob SSCommitted Points: 1720 More actions · Answer 4

We monitor cluster failover using the SQL errorlog info--using a technique like someone described above--basically by running a process that reads in and checks the SQL error log every 15 minutes or so.

In Transact SQL you can use the command

EXEC master.dbo.xp_readerrorlog

to read the SQL error log. Just dump the output into a table. Scan the data looking for a string that typically appears at the top of a new error log -- e.g.:

"Microsoft SQL Server"

then parse off the date/time from that row. If the date/time is recent, you'll know that a cluster failover may have occurred! You could then use "xp_sendmail" or RAISERROR to create an alert notification.

- john

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 5

Thank you Sa24 and John for your ideas! I immediately will try your suggestions

ChrisMoix SSCommitted Points: 1568 More actions · Answer 6

I always created a job to send me an email and set the schedule to run when SQL Server Agent starts. That way I knew when a virtual server failed over or if there was a problem with the SQL Server Agent restarting on a node. The only downside to this is if the SQL Server Agent fails to restart on the secondary node. If this is too much of a problem, you probably should purchase some software like Servers Alive or something similar.