solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Customized Alerting with Spotlight on SQL Server

MSSQLTips author Greg Robidoux By:   |   Read Comments (2)   |   Related Tips: More > Monitoring
Problem

Managing SQL Server requires many tasks to be performed by DBAs. These include making sure databases are backed up, performance is acceptable, releasing updates into production, security changes, SQL upgrades, adding databases, monitoring and more.  Some shops have multiple DBAs or DBA teams that are responsible for various duties where other shops have one person performing all of these roles and in some cases even more.

Maintenance and monitoring are not the most fun aspects of being a DBA, but without doing these tasks your headed for disaster. Maintenance duties can be setup to run on an automated schedule, but monitoring takes some human effort to ensure real issues are addressed and the not so critical items are ignored. In order to have a proper monitoring system you need to identify what needs to be monitored, how you are alerted and what items are critical. 

Using T-SQL and SQL Agent you can setup jobs to capture critical information, but it is not as easy to set limitations on when you are notified and how frequently you are notified.  In this tip we look at one solution that will help you better manage critical alerts for your SQL Server environment and be notified when needed.

Solution

Spotlight on SQL Server Enterprise version 10.0 introduced a new feature called Alarm Actions that allows you to setup specific criteria as to when an action should take place based on how an Alarm (alert) is configured.  This allows you to focus on the real issues and not be bothered by non-issues or less critical issues.

Alarms and Alarm Actions

One example of this might be when CPU has reached 90% for a period of 5 minutes during business hours then an email notification should be sent to the DBA team.  During non-business hours you may have a highly intensive CPU operation that runs every night which you know is not critical and you don't want to be notified.

Alarms - Spotlight on SQL Server Enterprise

In Spotlight on SQL Server, Alarms are setup that are triggered by some specific event that occurs on your server either at the Windows level or within SQL Server.  Here is a sample list of some of the different types of Alarms for monitoring.  To learn more about the different types of alarms review this page.

Spotlight Alarms

Once the alarm has been configured you need to setup Alarm Actions to take some action when an alarm is triggered.

Alarm Actions - Spotlight on SQL Server Enterprise

The Alarm Actions allows you to build criteria as to when an Alarm is fired based on several factors. In the example above, the Alarm Action was based on CPU usage at a specific percentage for a specific length of time and then to notify the DBA team only during business critical hours. Another example might be no full backups have occurred in the last 2 days for production servers, so the action is to run a full backup for those databases.

The Alarm Action is broken down into three components: Conditions, Actions and Descriptions.

The Conditions allow you to determine what criteria determines if an alarm action is fired.  So in the example below three conditions have been set.  The first The alarm is... identifies what alarms make up this action, this could be one or many. The connection's tag is... allows you to setup tags that group specific servers together such as production, development, etc...  The day of the week is... allows you to determine which days of the week this alarm should be fired, here we selected all days except Saturday.

The Actions section allows us to determine what to do if all of the conditions are met.  For this example we selected Send email to..., which will send an email.

The Descriptions section allows us to further edit certain items as well as see an overall description for the Alarm Action.

Spolight on SQL Server Alarm Actions Example

Here is another example of an Alarm Action based on Page Life Expectancy that has occurred for at least 15 minutes and the action is to write to the Spotlight on SQL Server log.

Spolight on SQL Server Alarm Actions Criteria

Summary

From the two examples above, we were able to further define when an action occurs based on several sets of criteria instead of just a counter or measure reaching a specific threshold.  This allows you to further hone in on the mission critical servers as well as critical items that need to be addressed by the DBA team.

As you can see from the Alarm Actions editor there are several Conditions and Actions that can be taken besides just sending out an email message. Trying to replicate this level of complexity would take a great amount of work if you did this with T-SQL and SQL Server Agent.  The beauty of the Alarm Actions is that you can setup the Alarms once and then build different criteria for more critical servers, so you can focus on what is really important versus getting overwhelmed with email messages and alerts and eventually ignoring everything which is as effective as having no monitoring solution in place at all.

Next Steps

MSSQLTips.com Product Editorial sponsored by Dell Software makers of Spotlight on SQL Server Enterprise.



Last Update: 1/14/2014


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, February 25, 2014 - 11:41:29 AM - Greg Robidoux Read The Tip

Hi User,

if you are using Spotlight on SQL Server Enterprise then yes this would be one way of doing this for the Alerts.  Can you setup the same alerts, but then determine which servers are critical.

You could also do this by coding your own alerts in SSMS and only turn those on for critical servers.


Monday, February 24, 2014 - 5:55:20 PM - user Read The Tip

Is this the only way of differentciating between alerts for production which are urgent, and alerts for test which are not as urgent?




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.