Published on February 17, 2014
Customized Alerting with Spotlight on SQL Server Greg Robidoux SQL Server Professional SQL Server DBA Professionals Global Marketing
This review is based on the FREE SQL Server performance monitoring tool: Spotlight on SQL Server. 2 SQLDBApros
Managing SQL Server… Managing SQL Server requires many tasks to be performed by DBAs, including: • • • • • • • • 3 Making sure databases are backed up Performance is acceptable Releasing updates into production Security changes SQL upgrades Adding databases Monitoring And more… SQLDBApros
Maintenance & monitoring… Maintenance and monitoring are not the most fun aspects of being a DBA, but without these tasks you are headed for disaster. Maintenance can be set up on an automated schedule. Monitoring requires human effort to ensure the real issues are being looked at and the non-critical items are put on the back burner. 4 SQLDBApros
Maintenance & monitoring… 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. The Problem: You can setup jobs to capture critical information in T-SQL and SQL Agent, but it is not as easy to set limitations for when and how frequently you are notified. 5 The Solution: Spotlight on SQL Server Enterprise. SQLDBApros
Spotlight on SQL Server Enterprise • Alarm Actions – Allow you to set up specific criteria as to when an action should take place – Based on how an alarm (alert) is configured • Focus on the real issues and not be bothered by non-critical items. Example: When CPU has reached 90% for 5 minutes or more during business hours, then an email might be sent to the DBA team. You might have a highly intensive CPU operation that runs every night, which you know is not critical and do not want to be notified. 6 SQLDBApros
Alarm Actions – Conditions Conditions build criteria that determine if an alarm action is to be fired – • The alarm is… – Identifies what alarms make up this action (one or many) • The connection’s tag is… – Setup tags that group specific servers together (production, development, etc.) • The day of the week is… – Determine which days of the week the alarm should be fired. 7 SQLDBApros
Alarm Actions – Actions Actions allow us to determine what to do if all of the conditions are met – • Send email to… – Which will send an email to specified recipient 8 SQLDBApros
Alarm Actions – Descriptions Descriptions define the rules for the alarms– 9 SQLDBApros
Example: Page Life Expectancy Here is another example of an alarm action based on page life expectancy that has occurred for at least 15 minutes with an action to write to the Spotlight on SQL Server log. Conditions – 10 SQLDBApros
Example: Page Life Expectancy Actions – 11 SQLDBApros
Example: Page Life Expectancy Descriptions – 12 SQLDBApros
Hone in… Based on the examples, 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 certain 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. There are several conditions and actions that can be taken besides just sending an email – Trying to replicate this level of complexity would take a great amount of work in T-SQL and SQL Server Agent 13 SQLDBApros
The beauty is… You can set up the alarms once and then build different criteria for more critical servers so you can focus on what is really important. 14 SQLDBApros
Download a FREE TRIAL of Spotlight on SQL Server and start using Alarm Actions today! 15 SQLDBApros
Learn More View Greg’s Original Post Download Spotlight on SQL Server 16
By: Greg Robidoux | Read Comments (2) | Related Tips: More > Monitoring Problem. Managing SQL Server requires many tasks to be performed by DBAs.
Home » Products » Spotlight on SQL Server » Spotlight on SQL Server - Blog » Customized Alerting with Spotlight on SQL Server
Recently MSSQLTips Greg Robidoux wrote an editorial on Spotlight. Here is some of what he said. Problem Managing SQL Server requires many tasks to be ...
Monitoring is a key part of DBA activities and being alerted only when necessary helps you focus on the real issues and not false alarms. Read this tip ...
(Greg Robidoux) Managing SQL Server requires many tasks to be performed by DBAs. These include making sure databases are backed up, performance is ...
Automate SQL Server Monitoring with Email Alerts; Customized Alerting with Spotlight on SQL Server; How to setup SQL Server alerts and email operator ...
Spotlight on SQL Server Enterprise; ... Customize for your environment. ... Spotlight supports all SQL Server sort orders, ...
View 100 Spotlight On Sql Server posts, presentations, experts, and more. Get the professional knowledge you need on LinkedIn.