If I've learned one thing with my on premise versions of SQL Server, it is to automate as much as possible. I have automatic alerts set up for Error Severity Levels 16 through 25 and 823, 824 and 825 (thanks to Brett Ozar and this post). Nightly, I get the server to send me an email that shows me which databases have the greatest change in size over the last couple of days as well as how much space I have available on the server. While I was tweaking one of these jobs the other day, I got to thinking about how I could increase the automation that I have in my Azure SQL Databases.
Alerts in Azure SQL Database
One of the easiest ways to gain some automated insight into what is going on in your Azure SQL Databases is to set up alerts on your databases. You can set up alerts for the following conditions:
Blocked by Firewall
Log IO percentage
Data IO percentage
Total Database Size
Database Size Percentage
In the next section of this article, I will set up alerts for CPU Percentage and DTU Percentage and then put the database under load so that these alerts get triggered.
Setting Up the Alerts
The first thing that must be done is to navigate to the area of the Azure Preview Portal where you can set up the alerts as shown below.
Setting Up the Alerts
Next we will add out first alert for when the DTU Percentage is greater than 50%. We will also add an email there so that we are automatically notified when the threshold has been breached.
DTU Percentage Alert
Finally, we will add another alert for when the CPU Percentage is greater than 20% (I won't show this one as the process is quite similar). When we are finished, we can see that there are 2 alerts that have been created.
DTU Percentage and CPU Percentage Alerts
Now it is time to put the server under a bit of load in order to see the alerts in action. I am a big fan of using Eric Darling's number's table to generate load test data. It doesn't use any of those "inherently evil" loops and is really easy to tailor. I've modified the script to insert 1 million records into my Customers table as shown below.
Numbers Table Script to Insert Data
If we modify the chart for the database, we can definitely see that the DTU Percentage has crossed 50% and the CPU Percentage has crossed 20% as shown below.
CPU and DTU Percentage
And when we check our email, we can see that our database has let us know that it has been a bit stressed lately.
Alert Email Notification
It is really easy to set up Alerts in Azure SQL Database, and can be a big help in letting you know what is going on your server. Let me know in the comments about any Alerts that you have configured, and the threshold settings that you've used.