In a previous post, I explained how to customize alarms in Spotlight on SQL Server Enterprise. Recently, I had a customer inquire how he could refine the "Days Since Last Full Backup" alarm. He wanted to be able to exclude specific databases, but specify different thresholds for different databases. For example, user databases are backed up every 3 days, but system databases are backed up every 10 days. How do we refine the Spotlight alarm so that we're alerted to backup issues specific to the databases and their backup schedules?
Fortunately, many alarms implement "Key Values" that can be used to achieve this level of customization. The "Key Values" are enabled for alarms that support them and are displayed in the alarm customization screen:
When you click the Add button, a dialog box appears that allows you to start typing text:
In this specific example, I see a list of databases contained within the monitored connection. I simply choose the database and click the OK button. I repeat this process for every database relevant to my desired customization:
This is where things get interesting. Every entry in my "Key Value" list has its own collection of alarm severities. This means that I can further customize threshold values for each database in my list. I choose the "Key Value" entry, from the list, click the Add Severity button, and add the relevant value to the newly created severity:
In the above example, I specify that if the msdb database has not had a full backup taken in the last 10 days, then a High severity alarm should be raised by Spotlight. I apply this methodology to every database in my "Key Value" list to achieve the desired customization!
If there are databases that will never have a full backup and you wish to exclude them from the alarm, you click the "values" hyperlink, at the bottom of the alarm customization dialog, and add them to the list:
If you're interested in learning more about Spotlight, please visit SQL Server monitoring tool.
