Receive SQL Server Alerts through Slack

By:   |   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.

pager duty

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:

command prompt

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.

last step

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

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Maria Zakourdaev 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



Comments For This Article




Friday, July 10, 2020 - 4:27:18 AM - JonathanS Back To Top (86115)

Hi, I know this is an old thread BUT I have this working in a stored proc but its seems to be in an infinate loop, it posts the same message over and over. I've looked at the slacker api and i'm not sure why....do you know how to ensure it only posts the message once?


Thursday, June 28, 2018 - 5:15:14 AM - Maria Back To Top (76443)

Hi Rana, If you have no internet than you will not be able to contact Slack api. Please consider spinning up a smal sql server instance that is connected to internet and use linked server to execute scripts that require internet connection.

Best Regards

Maria


Monday, June 25, 2018 - 4:35:57 PM - Kartar Rana Back To Top (76393)

Maria,

Excellent article!!! I am still struggling to get a few things sorted though, but wanted to highlight, if they are worthwile.

"slack = Slacker()" should this not be "slack = Slacker('')"

And also, if i am doing this on a machine without internet, do you happen to know how can i get slacker installed?


Monday, June 18, 2018 - 11:33:08 AM - Angel Back To Top (76241)

This is good to know when we upgrade. For older versions of SQL Server we used the webhooks from Slack and Microsoft Teams to do the same. I then wrote a stored procedure that sends the messages upon job or stored procedure failures.


Sunday, June 17, 2018 - 8:22:57 PM - Negar Back To Top (76239)

Thanks Maria for sharing,

I didn't know about Pyton and Slack and I'll give it a try. 

I had the same idea but I found another way to impliment it, as we can configure slacks channel to have an email address, I created a new opeartor with SLACK email, and then whenever I want to send notification, report or anything to Slack I use that operator or the email that has been attached to the slack


Friday, June 15, 2018 - 12:37:41 AM - wondi Back To Top (76226)

I want to install and configure slacker















get free sql tips
agree to terms