SQL Server Performance Forum – Threads Archive
Notifications and Failbacks
I have recently configured a SQL Server 2000 EE 64-bit active/passive 2-node cluster on two servers running Windows 2003. The hardware is identical on both servers.<br />The cluster is running perfectly. All the clustering tests have passed as per Brad McGehee’s useful articles on this site.[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br />Two questions:<br /><br />(1)<br />I would like to be able to receive an active notification (by email for example), if my cluster fails over to the other node.<br />Does SQL Server 2000 or Microsoft Cluster Services support such functions?<br />I want to detect when an actual cluster failover occurs and not simply whether both nodes are responding to ‘PING’ requests from other remote servers.<br />Can I set up a SQL Agent alert notification for this?<br />If so, what event(s) should the SQL Agent be monitoring?<br />I have considered programming my own application that would, for example, monitor the Windows or SQL server event logs to detect log entries that signal a failover.<br />Is there a simpler solution?<br /><br />(2)<br />Failbacks:<br />My Cluster Service Group is currently set to ‘Prevent failback’.<br />If the hardware of two nodes are identical, and the performance of the two nodes is therefore identical, is it necessary or advisable to always initiate a failback when a failover occurs? If so, for what reasons?<br />Is it better to move the group manualy once the primary node has recovered or is it better to automate the failback?<br />If automated, should the failback attempt be immediate or is a time delay advisable?.<br />Opinions on this are appreciated.
1.
You could use some standard monitoring tools to check the SQL server service and send an alarm if it stops (and starts) since this is what will happen during a failover. For custom stuff you could schedule a VB script in SQL Server Agent that checks who is the current owner of the node. If it change you could trigger an alarm. See:
http://groups.google.se/[email protected]&frame=off It only works on Windows 2003 though. On Windows 2000 you could write a script that check the IPs on the node that currently is running the SQL Server and Agent services. If it’s not the IP of the default active node then trigger an alarm. 2.
I never set it to initate failback on a active/passive cluster. Instead if an alarm comes then I go in and check event and error logs to identify what caused the failover. I only fail back if the original node is ok. Might want it different on active/active clusters if a single node isn’t really capable of running all services alone for a long time. But I wouldn’t want the cluster service to keep try and fail back all the time if I had corrupt memory or something on a node.
To know about cluster failover as soon as it happens, you can create one stored procedure that will contain notification code and need to mark this stored procedure to run when SQL services starts up. After creating this stored procedure, whenever cluster will failover OR we will restart server/ SQL services it will send a notification email.
— CODE OF STORED PROCEDURE AND SETTINGS USE master
GO
CREATE PROCEDURE SQLServiceRestartNotification
AS
EXEC master.dbo.xp_sendmail
@recipients =’[email protected]‘,
@subject =’Cluster Failover/ SQL services restarted !’
GO USE master
GO
EXEC sp_procoption ‘SQLServiceRestartNotification’, ‘startup’, ‘true’
Please Note: This stored procedure will work when SQL service will be started after stop, not when stopping. and SQLMail should be already configured on server. After every SQL Services restart this stored procedure will be called and mark an entry in SQL Error log file like below: – Launched startup procedure ‘SQLServiceRestartNotification’ Deepak Kumar –An eye for an eye and everyone shall be blind
Thanks for the procedure, Deepak, but it will only be useful on my 32-bit servers.
xp_sendmail is not supported in 64-bit Edition. Argyle has some good ideas though. Thank you both.
Instead of xp_sendmail you could insert a row in a table used as a mail queue and then have a standard vbscript going through this table every minute and send an email if a row exist. http://www.sqlteam.com/item.asp?ItemID=5908
]]>