The Alert Configuration window allows you to set up alerts for specific SQL Server instances. To open the Alert Configuration window with a SQL Server instance selected, right-click the instance, and then select Configure Alerts. You can set the acceptable thresholds using the following alert types:
Acceptable threshold where SQL Diagnostic Manager does not generate an alert.
Informational threshold where SQL Diagnostic Manager generates an informational alert.
Warning threshold where SQL Diagnostic Manager generates a warning alert.
Critical threshold where SQL Diagnostic Manager generates a critical alert.
In addition to setting the thresholds for specific alerts, you can customize your alerts by adding comments to each alert. Comments allow you to include instructions to users when a level reaches a particular threshold or to provide additional information about the alert. You can also customize the levels at which SQL Diagnostic Manager provides alert notifications by changing the informational, warning, and critical values either on the Configuration tab of the Alert Configuration window or, for database- and disk-level alerts, on the Database Threshold Configuration window available by selecting the database or disk, and then clicking Edit.
You can also base your alerts on the past performance of the metrics collected by SQL Diagnostic Manager. This is a powerful and effective way to make sure that the alerts you receive are outside of your typical metric ranges.
For each alert metric, you can enter the specific criteria by double-clicking the values on the Configuration tab.
To configure an alert:
- Right-click a SQL Server instance in the Servers tree.
- Select Configure Alerts.
- Select the metric you want to edit from the list in the Alert Configuration window.
- If the metric features a per-database or per-disk alert , on the Configuration tab of the Alert Configuration window, click Add. Use the drop-down list to select the database or disk to which you want to apply these settings. If the metric applies at the instance level , continue with the next step.
- Check the boxes next to Informational, Warning, and Critical to include alerts for these states.
- Change the alert thresholds by moving the arrows to the appropriate levels or by double-clicking the value and typing a new threshold level.
- If the metric features a per-database or per-disk alert , click Advanced to apply any advanced settings, such as alert suppression or autogrow settings. Click OK. If the metric applies at the instance level , continue with the next step.
- Select the Comments tab and enter information you want displayed in the alert message for this metric.
- Click Apply.
- If you want to replicate these edits to other SQL Server instance SQL Diagnostic Manager is monitoring, tags, or templates , click Yes.
- Click OK to accept your changes.
Note that when you modify an alertable metric in the Alert Configuration window, the Create Template button is disabled. Once you apply the modification, this command button is enabled again.
A full refresh of alerts excluding table fragmentation occurs in the following circumstances even if longer collection intervals are defined:
- when the collection service starts
- when the collection service receives a new workload, such as redirecting the Management Service to a new Repository
- when you add a SQL Server for collection
- when a SQL Server resumes from Maintenance Mode
- when a user selects Refresh Alerts
This refresh does not cause collection of non-alertable data, nor cause collection of anything that is disabled.
Table Fragmentation alert thresholds
SQL Diagnostic Manager updates Table Fragmentation alerts with new threshold information only when new data is generated during the table statistics collection interval. It is preferred that you set your table statistics collection interval to occur when your server is not performing any production tasks.
Because this data collection occurs at most once per day and you can configure it to run as infrequently as once per week, you may experience a significant delay before new alerts are generated with your new alert threshold. You can configure your wait monitoring using the Monitored SQL Server Properties window.
Apply different alert thresholds to multiple databases or disks
Some of the available alerts allow you to apply them independently to multiple databases or disks on your monitored SQL Server instance. When you click the alert to modify the thresholds, notice that a list of your databases or disks appears with the associated thresholds as they are currently set. Click Edit to make your changes. The Database Threshold Configuration dialog lets you specify the database, and then select the thresholds for that database only. The Disk Threshold Configuration dialog provides the same functionality, only disks replace the databases.
SQL Diagnostic Manager allows you to configure generic alert settings as a template that you can apply to servers and groups of servers in your organization. Click Tools > Alert Configuration Templates and complete the required fields to configure an alert template.
Informational alerts allow you to set a threshold that, when generated, triggers a status that does not affect the overall status of the server within SQL Diagnostic Manager. You can use informational alerts to notify an administrator of the state of a particular metric for a server or trigger secondary processes that could take action to prevent issue escalation.
Note that for certain metrics, using the informational alert means that you no longer receive a warning or critical alert for events generated by that metric. Please review the situation before setting up an informational alert.