Receive SQL Server Alerts through Slack

By: Maria Zakourdaev   |   Updated: 2018-06-14   |   Comments (6)   |   Related: > Monitoring

Problem

The main channel for DBAs to get notifications from SQL Server is an email. We usually email alerts in case of a cluster failover, server performance condition alerts, error log messages, job failures, etc.

However, our email inboxes are frequently overloaded with spam messages and internal company communications and the most important failure alerts can easily get lost.

Solution

I was looking for additional channels that can help my team to get alerts from SQL Server. After Microsoft added Python support with SQL Server 2017, I suddenly had an interesting idea. Using a small Python script, we can forward our messages or alerts to Slack.com.

We were already using Slack in my company for a while, mainly for team collaboration. It is a very nice cloud messaging service and has many pricing plans, including a free plan.

How is this solution better than email?

Slack’s main idea is to take the team’s communication to the next level. You can send alerts to the specific person or to the channel which can contain many members. We can create channels for different servers, applications or projects and have conversations among channel members about the received alerts. We can also send messages to another team’s channel and notify them if the alert has any impact on them. All messages in every channel become indexed and searchable once inside Slack, giving the team access to a collective knowledgebase.

Slack integrates with many apps and services that you may already use. If you are using some incident management tool, like PagerDuty, ServiceNow or JIRA, you can see all notifications in the same Slack channel. For instance, see below when someone acknowledges the incident and when the incident gets resolved.

The main advantage of sending the alerts to Slack is that you can install the Slack application on your cellphone and get notifications on anything that is happening in your channels.

Small Python script for a big change

I will show you how to create a stored procedure that will send a message to a specific Slack channel. You will be able to use it in any stored procedure or in any job.

Install Python slacker package

First, you need to install the Python package named slacker. Open a command line utility as an administrator, go to the below path and use pip to install the package. Pip is a Python package management command line tool.

cd C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\Scripts
pip install slacker			

Here is what will happen after running the above commands:

Create T-SQL stored procedure with Python code

Here is the script to create our procedure. We will use sp_execute_external_script, which is a new SQL Server system stored procedure that enables usage of the Python language inside T-SQL code. To enable this functionality, you need to execute the below command:

EXEC sp_configure 'external scripts enabled',1			

Our procedure will use a very short Python code, only a few lines. Python is a very easy-to-learn and easy-to-use language. The most important thing about Python is that blocks of code are denoted by line indentation. When you copy the below code, make sure you are not adding any indentation to make it look “prettier”, the code will not work.

We will pass 2 parameters to the Python code. The Slack channel @slack_channel_in it should post the message @message_txt_in. Please note, that the sp_execute_external_script procedure declares variables in T-SQL format @variablename, however, inside Python code, you will use the <variablename> without an @.

DROP PROCEDURE IF EXISTS AlertToSlack
GO

CREATE PROCEDURE AlertToSlack @message_txt varchar(4000), @slack_channel varchar(256)
AS
BEGIN
EXEC sp_execute_external_script @language =N'Python',
@script=N'
from slacker import Slacker
slack = Slacker(<put here your slack id>)
slack.chat.post_message(slack_channel_in, message_txt_in)
',
@params = N'@message_txt_in varchar(4000), @slack_channel_in varchar(256)',
@message_txt_in = @message_txt,
@slack_channel_in = @slack_channel
END			

After creating and executing the stored procedure, we can see some of the messages below in the Slack channel.

Now you will never miss an important alert. Just make sure your cellphone battery is charged and the sound is not muted.

Next Steps

• The Python interface for the Slack API
• Running Python using T-SQL tutorial
• SQL Server 2017 and Python Tutorial

About the author

Maria Zakourdaev has been working with SQL Server for more than 20 years. She is also managing other database technologies such as MySQL, PostgreSQL, Redis, RedShift, CouchBase and ElasticSearch.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips