Database administrators have a lot of tasks and responsibilities but perhaps the most challenging task is performance tuning. Because performance tuning issues can cause huge problems in the business area, database administrators have to monitor performance metrics and have to get immediate, if not sooner, notifications when the performance values cross an optimal state. Generally, this notification comes by e-mail.
In our business lives, we follow our e-mails regularly but some issues are more significant than others and we need to respond quickly. On the other hand, communication platforms rapidly change and software vendors offer new application solutions to users. Therefore, sometimes traditional e-mail communication is not enough for us. The software vendors who become aware of this situation release new applications. These applications are becoming more popular. Because these applications offer or claim to us a simple, clear and understandable communication system. Due to above-mentioned features, if we get database performance notifications over these applications, thus situation provides greater flexibility and speed. Because of all these reasons, in this article, we will integrate Azure SQL alert notifications to Slack. We will use the below architecture diagram to achieve this integration.
Now, we will learn something about technologies which we will use in this integration.
Slack: Slack can be defined as a team communication application. Slack is used in many companies, especially where information firm usage is very high. Slack supports more than 30 programming languages and team members can share code, text very easily. At the same time, you can use this software in mobile devices.
Webhook: Webhook is an HTTP callback which provides transfer information applies to other applications. The most important feature of webhook is the ability to transfer data real time. When an event is fired in an application, webhook sends this data to another application.
Pagerduty: We can define Pagerduty like this. Pagerduty is a service based operations performance platform which you can handle incidents and notifications in your business environment. Pagerduty can integrate a lot of monitoring services or tools. Pagerduty has advanced notification capability. It provides phone, e-mail or SMS notifications. At the same time, you can create and customize notification rules.
Create an alert rule for Azure SQL
Azure SQL offers a lot of feature for cloud databases. One of these features is the “Alert Rule”. Now we will look at them; how to create a database alert rule.
After login to Azure portal, we will navigate SQL databases and then click on “Alert Rules”.
We will click “Add alert” and will reach “Add an alert rule” screen.
In this screen, we have to fill some fields. This field has following definitions
Resource defines the name of SQL Server database name
Metric defines the performance counter where you want to get a notification
Condition and Threshold define the formula of alert
Period defines how long time the metric remains in this condition
Email owners, contributors, and readers defines when the notification triggered these stakeholders will get an e-mail about notification
Additional Administrator email(s) defines if you want to add additional emails you can add here
Webhook defines where you can provide your webhook URL for which you want to transfer alerts.
Now we will complete definition about alerts. For this demonstration we will select “Failed Connections”. When somebody tries to connect Azure SQL and gets a connection error, this alert will be triggered. Also, you can find another metrics in this menu. These are;
- Blocked by Firewall
- Failed Connections
- Successfully connections
- CPU percentage
- Deadlocks
- DTU (Database Throughput Unit) percentage
- DTU (Database Throughput Unit) limit
- DTU (Database Throughput Unit) used
- LOG IO percentage
- Data IO percentage
- Sessions percentage
- Total database size
- Workers percentage
- In-Memory OLTP storage percentage
As we need a webhook URL, we will provide this URL in Pagerduty. For this reason, we will login to Pagerduty and will set Pagerduty service settings and Slack integration.
Create service for Azure in Pagerduty and integrate to Slack
In this part of the article, we will log into to Pagerduty because we need the webhook URL. This URL provides communication between Azure and Pagerduty. When the notification is triggered, this alert information transfers to Pagerduty immediately. Another note about this communication is this data transfer uses JSON format.
Now, we will log into Pagerduty and then will navigate “Configuration” and click “Services”.
We will click add news services and will select Azure.
We will click “Add Service” and service will be created. This service provides webhook URL.
In this step, we will integrate Pagerduty service to Slack.
We will click “New Extensions” and will select “Extension Type” as Slack. You can find another application for integration (JIRA, ServiceNow, Hipchat etc.)
We will write slack account name and then click “Authorize”.
In order to complete this step, Pagerduty will redirect to the authorization page.
In this screen, we can select Slack channel where our messages will come. We have completed Pagerduty and Slack integration. Now, we need webhook URL. We can find this URL under the service “View” menu.
Now, we will return Azure portal and then copy this URL to Azure alert screen webhook field. Finally, we will click “OK”
After, we create an alert we can see this alert rules in the “Alert Rules” panel.
When a notification occurs you can see this notification on Pagerduty web page.
At the same time, this notification will be sent to Slack. When I will log into my mobile phone Slack application, I can see the notification.
The above image was taken from a mobile device. In this image, we can find information about notificatiosn or you can change activity type “Acknowledge” or “Resolve” and this activity type information will be sent to Pagerduty.
Conclusion
This article’s goal is to combine the powerful benefits of different products. When we go back to the architecture diagram, you can see that, in fact, we have combined three different products to improve the speed, flexibility and ability to respond to monitoring alerts.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023