Setup SQLServer Alerts with Open Source SQLMonitor

If you are responsible for managing & monitoring SQLServers, then it is mandatory to have monitoring and alerting for critical issues of your SQL Servers.

For this purpose, open source SQLMonitor now has built-in Alert Engine. With the capability of this alert engine, alerts can be sent to Slack & Email to various teams. The following are some advantages –

• Alert Engine has the capability to send alerts to Slack, PagerDuty or Email
• Alerts can be sent to various teams including the DBA team
• To avoid alert spamming, the Alert Key method is used to identify alerts uniquely
• Alert Engine uses Slack Threads for updates thus avoiding spamming on Slack.
• Each update on the alert is saved in the alert history table. Thus, there is no loss of any alert details.
• Alerts auto-clear if the issue is gone, and auto-resolve after threshold time.
• Alerts can be Acknowledged, Cleared, Suppressed & Resolved from Slack itself.

Alert Engine is built using Python & SQLServer. Adding new alerts is very easy.

At the time of writing this blog, I have added 14 core alerts include blocking, ag latency, CPU, memory, disk latency etc.

How to Setup Alert Engine

To setup the alert engine, following steps are required –

• Create slack bot following this Youtube Playlist.
  • After completing the tutorial, we should be able to send slack messages.
  • Sample python code for testing slack messages – Python-Scripts/Send-SlackAlert.py
• Setup Credential Manager on SQLServer. We save & retrieve sensitive details like slack tokens, pagerduty api etc in this.
  • Using Credential-Manager/SCH-[dbo].[credential_manager].sql, create credential manager table
  • Create required procedures
    • Credential-Manager/SCH-[dbo].[usp_add_credential].sql
    • Credential-Manager/SCH-[dbo].[usp_delete_credential].sql
    • Credential-Manager/SCH-[dbo].[usp_get_credential].sql
    • Credential-Manager/SCH-[dbo].[usp_update_credential].sql
  • Set appropriate permissions using Credential-Manager/SCH-Permissions.sql
    • While creating the certificate, we should use some strong Encryption Password
  • Sample usage – Credential-Manager/QRY-Usages-CredentialManager.sql
• Create alert engine related objects
  • Create tables & add slack_bot_token etc to Credential manager.
    • Alerting/SCH-Create-Alert-Objects.sql
    • This creates tables like dbo.sma_oncall_teams, dbo.sma_alert, dbo.sma_alert_history etc.
    • Add DBA team entry in table dbo.sma_oncall_teams preferrably with alert_method slack.
  • Create all procedure objects. This list may update in future.
    • Alerting/SCH-[usp_get_active_alert_by_key].sql
    • Alerting/SCH-[usp_get_active_alert_by_state].sql
    • Alerting/SCH-[usp_get_alert_by_id].sql
    • Alerting/SCH-[usp_insert_sma_alert].sql
    • Alerting/SCH-[usp_update_alert_slack_ts_value].sql
  • Update details in dbo.sma_params table relevant to alert engine.
    • select * from dbo.sma_params p where p.param_key in ('dba_slack_bot','dba_slack_channel_id','dba_slack_channel_name')
    • update other params also in dbo.sma_params including dashboard & panel urls
• Setup a webserver running core python script Alerting/Run-SQLMonitorAlertEngineWebServer.py
  • This script has built-in scheduler to running various alerts at 5, 10 & 15 minutes schedule.
  • Also, it runs the webserver at port 5000 using flask for testing purpose.
  • I am using ngrok in my lab environment to setup & listener to slack events for interactive slack messaging.

With monitoring & alert feature, SQLMonitor can serve as an enterprise monitoring tool. I have tested SQLMonitor with a total of 200+ servers. It works seemless without any issue.

I hope this will be helpful to anyone looking for open source SQLServer monitoring and alerting tool. If you like the blog, do like and subscribe. Thanks.