SyntaxHighlighter

Monday, February 10, 2014

SQL Server Agent Restart Notification / SQL Server Database Cluster Failover Notification

I work in an environment running a SQL Server Cluster (2-node, Active/Passive) on the production server. I want to know when the SQL Server Cluster fails over or when it's tested. It seems to have happened a few times. Since our IT department is somewhat regimental, I don't have access to a lot of system administrator functions. However, there is still a simple way to get that notification.

When I was researching this problem, I came across this:
SQL SERVER: How Can I Get Notification Alert When Fail Over Happened? This got me started down the right path but it turns out to be simpler.

First, a bit of background on the fail over. When fail over occurs, the clustering services on the remaining nodes detect that failure. They attempt to shut down services on the failing machine. As soon as that completes or when the machine is deemed inaccessible, the clustering service starts SQL Server services on the new active node. We can take advantage of that stop service/start service process because SQL Server Agent has a hook in its scheduler that can start a job when SQL Server Agent starts (see the screenshot to the right).

Here are the steps:
  1. Create the new job.
  2. Name it something like "Agent Restart Notification".
  3. Add at least one step. 
    • It can be an empty step with some do-nothing code in it, but it has to be there (otherwise the scheduler will not enable the job).
  4. Add a schedule, but choose Start automatically when SQL Server Agent starts.
  5. On the notifications page, send an email when the job completes.
    1. Please note that you must have Database Mail enabled and properly configured.
    2. You must also have an Operator properly configured.
  6. Save the job.
That's it. Next time the SQL Server Agent restarts, you will receive a notification. There's one problem here, of course. What if the SQL Server Agent restarts because of some other event, like someone manually restarting it? Unfortunately more complex code is necessary for such a specific event. To get started with that, see the above referenced blog post. As for me, this serves my purposes well enough. 

2 comments:

  1. The reason why millions have already switched to VoIP is because it offers the same service with additional services for a lower cost and efficiently. However, critics have raised a few concerns regarding the security of a VoIP service. The fact of the matter is that all the security threats that apply to an internet service apply to a VoIP service.

    ReplyDelete