Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
>>> >> > Make the most of SQL Server Integration Services Script Components < << <<<
 

Receive SQL Server Alerts through Slack


By:   |   Read Comments (5)   |   Related Tips: More > 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


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Maria Zakourdaev Maria Zakourdaev has been working with SQL Server for more than 15 years. She is also managing other database technologies such as MySQL, Postgresql, Redis, RedShift, CouchBase and ElasticSearch.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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

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

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

I want to install and configure slacker


Learn more about SQL Server tools