DBADash Alerting

[alinselicean]

Description
I had the following problem. One of the monitored servers (SQL2019) is running DynamicsAX and one of the main performance issues is facing, are excessive blocking.
So I decided to build an extension for DBADash to send alerts when excessive blocking is detected, using the template kindly provided here. Although it's still wip, here's how it works:

• blocking is collected every 1 min, then aggregated by DBADash
• I created a stored procedure inside DBADash DB that runs as a SQL Agent job every minute and reports on the findings
• currently it just sends email based alerts (HTML email)

Behind the scenes
All objects resides on a dedicated schema (ext), so any future upgrades (hopefully) will not impact it. The whole alerting framework is thought to be flexible:

• there's an ext.alerts table which will hold all the alert definitions, including an email template, slack / teams message template, various other settings
• there's an ext.alert_blockouts table which will hold all the blackout windows for the defined alerts
• there's an ext.alert_webhooks table which will allow alerts to be sent to IMs like Slack, Teams, basically anything that works with webhooks
• there's an ext.parameters table which holds all the runtime parameters, like individual alerting threshold, alert delay periods, etc
  And probably few more...

Future plans
Develop the framework so it will be able to send alerts not only via email, but also Slack, Teams and other platforms that support webhooks

Acknowledgments
Blocking alert template was provided by @DavidWiseman
Webhook support adapted from (can't remember / find the blog - I am trully sorry)

Wondering if there's an interest for this "extension" and if yes, I will make it available when it's finished.

[DavidWiseman]

Alerting is definitely an area that could be improved. 😊

There is an issue with creating the objects in the DBADashDB database though.

The dacpac is currently deployed using:
DropObjectsNotInSource = true,

This means that your changes will be removed from the DB the next time you upgrade. You could opt to handle the dacpac deployment manually.

It would be great if there was an option to ignore objects in a specific schema. I might consider changing it to DropObjectsNotInSource = false, at some point and handle cleanup operations manually.

[alinselicean]

Anyway, thanks for the heads-up :) I will reconsider the placement of these extensions in an external DB, at least for the time being.

[alinselicean]

Made some progress with the alerts and I realized I missed to mention few facts, rather important. But before anything, all the objects for the alerts are now out of the DBADash repository database.

Currently, the alerts are implemented as follows:

1. Each alert is having it's own stored procedure, although they share pretty much the same logic or steps
2. The stored procedures may have different behavior, depending on the nature of the alert. For example:
   • CPU alerts have an ON or OFF status per instance. Each alert will switch that status depending on the CPU levels and will alert only when that status changes.
   • Blocking alerts or disk free space alerts, on the other hand, do not have such status, so the alerts just "states the obvious" from the DBADash and will send the alert every time they run. The alert will be sent until the condition clears.
3. The alerts are implemented as SQL Agent jobs. One SQL Agent job for each alert. The frequency of each job is determined by the DBA:
   • CPU alerts would make sense to be executed every minute
   • Free disk space would make more sense to be executed every N hours, for example
4. Each DBA is free to add their own alerts, based on their needs or to change the existing ones, to better suit their needs.
   • one could use an existing SP as a source and change it to better fit the specific requirements
   • alternatively, additional SPs can be developed and just use the tables as a repository for the new alerts' metadata

If after all of the above, the interest is still present, I will post a link to the github repo once I have a stable version for the whole thing. @DavidWiseman if you want to have a look over the code, for whatever reasons, you'll be more than welcome. Just let me know.

The 2 alerts I already have in place (and fine-tuning):
High CPU Alert:

Blocked Processes:

[DavidWiseman]

Sounds interesting - A few people have asked about alerts. I've got some limited samples that people can use to create their own alerts but it would be good to be able to point people to something more comprehensive.

[alinselicean]

I now have 3 alerts. The 2 from above and I just finished the Disk Free Space.

I uploaded the file here: https://github.com/alinselicean/DBADashExt. The script does not deploy the required SQL Agent jobs, only the database, the objects and the static data (alert definitions, enums and some settings).

Feedback, good or bad (especially bad aka constructive) most welcomed.

[DavidWiseman]

The next version of DBA Dash will start to use DropObjectsNotInSource = false which will allow users to create their own object in the repository database. This change will be used to support custom reports and custom collections at some point in the future.