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.
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.
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.
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.
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.
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.