Auto notification for failover of a server and restart of SQL Server services


By:   |   Updated: 2009-01-12   |   Comments (7)   |   Related: More > SQL Server Agent

Problem
Our clustered Microsoft SQL Server environment hosts over 200 databases currently across all nodes.  Whereas clustering technology has been around for quite some time now, many applications still are not cluster-aware.  What does it mean to be a "cluster-aware" application?  Simply-put, it means that the application, upon failover of a database in a clustered instance, will attempt to re-connect to the database and re-establish the client connections that were instantiated prior to the failover of the database.  The problem we encountered was with a single application, provided and monitored by an outside company for the sake of tracking our heating and cooling systems' performance for temperature differentials.  This application is not cluster-aware and would fail whenever a failover occurred.  Since this solution is important, but not five-9's critical, we simply need to notify the vendor when a failover occurs.  While we could employ Microsoft System Center or Microsoft Operations Manager to alert when this event does occur, I would have had to enlist resources across multiple teams to get this accomplished under that plan.

Solution
The solution I employed was to simply create a job on the clustered SQL Server instance that will send an email via Database Mail to the vendor support email address, copying the Database Administrator on-call, and the Analyst on-call in the email.  I then assigned the job a schedule of firing whenever services start up.  This way every time the SQL Agent service starts an email will be sent notifying us that a restart of the service has occurred.

For those of you who are not familiar with the failover process of Microsoft SQL Server, the services are associated with resource groups located on a shared server volume (SAN/NAS).  The resource group is owned at any given time by a single physical server, referred to as a node in clustering terminology.  If a node experiences a shutdown request the resource group ownership is transferred from the failing node to another available node in the cluster.  This does involve cycling of the SQL Server services during this process and therefore would trigger the execution of this SQL Server Agent job when the services are restarted.  It would also occur if services are restarted for any reason, additional to a planned or emergent failover process, but that is appropriate for our needs in this case as well.

The next few screen shots will walk you through the process of setting this up via Microsoft SQL Server Management Studio.  You may choose to perform these tasks via T-SQL coding, but I find the job creation process works best from the SQL Server Management Studio GUI, then scripting the job from the Script button if you wish to later run this process on additional SQL Server instances.  At the end of this article I will outline some requirements for this process to work successfully in your environment.  By now, most of you have created a SQL Server Agent job; the purpose of this tip is to present how to handle remote notification of a SQL Server instance failover on a clustered environment, job creation is merely the process that has worked best for me and was simplest to implement.  Not too much heavy lifting in this tip - I promise.

SQL Server Management Studio Process

From within SQL Server Management Studio connect to the clustered instance you intend to configure, navigate to SQL Server Agent/Jobs, right click and select New Job... from the available pop-up menu as shown below.

Enter in values for Job Name, Owner, Category, and Description parameters. 

Navigate to the Steps page in the SQL Agent Job wizard and create a new Step.  Enter in the parameter values shown below (or values that conform to your environment.)  You will particularly need to enter correct coding for the Command parameter in this step.  My recommendation is to always test the command text within the context of a SQL Server Management Studio query before ever implementing it from within a SQL Server Agent job.  You will need to enter correct command parameter values (from your environment) for @profile_name, @recipients, @copy_recipients, @subject and @body for the call to dbo.sp_send_dbmail in the job command.  Information concerning SQL Server Database Mail and its associated stored procedures can be found in this 2008 tip on MSSQLTips.com.

Proceed onto the Advanced page of the Job Step form and enter the values below, substituting a Output file location that works best for your needs.  This may not be a function that you frequently use (the default is a null value) but I rely on this output file providing me with more-detailed logging than what is typically afforded via the default logging process of the SQL Server Agent.  This process was previously outlined on MSSQLTips here.

Click OK to save and close out of the Job Step form.  This returns you back to main Job creation forms for SQL Server Agent.  Navigate to the Job Schedule form and enter values identical to those presented below.  The values may seem inconsistent for Schedule type and Summary/Description, but the value for Description is read-only, and is unable to be modified.  Disregard it in this case.  This is the most-important screen.  It is the main reason that this process works for the purpose at-hand.  After making sure your command code is sound, making sure the job fires upon service start-up is critical.

Once this form is complete, save (or script) the job creation and you should be done.  I suggest first setting this up on a development or test cluster and test failover and determine if the job behaves as expected before you implement in production.

Now, there are some prerequisites that need to be in place before this process will work.  Those include:

Next Steps

  • Configure SQL Server Database Mail, if not currently configured and enabled.
  • Modify the command code to conform to your particular needs.  Test the command code outside of the context of a job from within SQL Server Management Studio
  • Create the Agent job as instructed above on a TEST or DEVELOPMENT SQL Server cluster if available in your domain; test failover for expected results.
  • Follow identical process in your PRODUCTION SQL Server cluster once testing results meet your standards for performance.
  • Download the script


Last Updated: 2009-01-12


get scripts

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

View all my tips
Related Resources





Comments For This Article




Friday, April 08, 2016 - 12:36:38 AM - Ron Back To Top

 Thank you very much.  Your script and solution worked well.  Thank you for providing it. 

 


Thursday, August 30, 2012 - 2:52:05 PM - SQLScottGleason Back To Top

Correction to the reply above, you should use

 

SET @ServerNodeName = CONVERT(sysname, SERVERPROPERTY('MachineName'ComputerNamePhysicalNetBIOS'))

 

 


Thursday, August 30, 2012 - 1:44:56 PM - SQLScottGleason Back To Top

/* There is a code template (works in 2005/2008/2008RS) that will help determine what node SQL is running on and send out an email alert if necessary */
/* SERVERPROPERTY   REF:  http://msdn.microsoft.com/en-us/library/ms174396(v=sql.105).aspx  */

DECLARE @ServerNodeName varchar(MAX);
SET @ServerNodeName = CONVERT(sysname, SERVERPROPERTY('MachineName'))

IF (@ServerNodeName = '')
 BEGIN
  print 'SQL is running on the correct node; no action necessary';
 END
ELSE
 BEGIN
  PRINT 'SQL Node has Failed over and is runnign on the incorrect node named ' + @ServerNodeName
  /* Write code here to do something; like sending out an email alert */
 END


Thursday, March 01, 2012 - 6:20:56 AM - bojanna mk Back To Top

Team,

 How can we know weather it was jusy a restart of the server or the instance was failoverd?Because in either case,we get this alert.


Tuesday, March 02, 2010 - 9:28:40 AM - yakaaithiri Back To Top

Is there a way to trigger a email when the SQL server agent service stops.

For restarting we can use this .

But if somebody stops the agent we need to be notified right ?

Please share if a have a way for it.

 


Monday, January 12, 2009 - 12:22:57 PM - timmer26 Back To Top

Very good.  You are absolutely correct and I like your workaround using creation date of tempdb since it is recreated upon restart.  I do not advocate using sp_procoption as I do not like to add extra processing upon a system restart.  Typically you want SQL to start as cleanly as possible.


Monday, January 12, 2009 - 11:19:27 AM - MohammedU Back To Top

The method you are using may give incorrect information becuase it will send the notification even in the case of SQL Agent restarted. I believe if you change your code to check the TEMPDB creation date is < a minute or two or the to check wheather it is SQL restart or agent restart might be better...

IF (SELECT DATEDIFF(MI, CREATE_DATE,GETDATE() ) FROM SYS.DATABASES WHERE NAME = 'TEMPDB') <= 2

BEGIN

EXEC
msdb.dbo.sp_send_dbmail

@profile_name = 'SQL Server Notification Service',

@recipients = '[email protected]',

@copy_recipients = '[email protected];[email protected]',

@body = 'This is an informational message only: SQL services possibly restarted on SQL01. Please restart any dependent application services after verifying status with DBA Team first.',

@subject = 'SQL Services Restarted on SQL01'

END

 OR

You can also create a procedure and set it to automatically Execute Stored Procedures When Starting SQL Server using sp_procoption procedure...

 



download


Recommended Reading

Querying SQL Server Agent Job Information

Querying SQL Server Agent Job History Data

Running a SSIS Package from SQL Server Agent Using a Proxy Account

Queries to inventory your SQL Server Agent Jobs

SQL Server Agent Job Schedule Reporting





get free sql tips
agree to terms


Learn more about SQL Server tools