By: Tim Ford | Updated: 2009-01-12 | Comments (7) | Related: > 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:
- Enabling Database Mail
- Creation of a valid Database Mail profile
- SQL Server Agent must be correctly configured as a service and running.
- Email accounts cited in the command text within the SQL Server Agent job must be valid.
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
About the author
Tim Ford is a Senior Database Administrator with MindBody.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2009-01-12
