By: John Miner | Updated: 2018-02-16 | Comments | Related: > Azure SQL Database
Problem
There has been a growing trend in which companies are moving away from on-premises software to the cloud, platform as a service, and other offerings.
Microsoft offers their standard database engine built upon a symmetric multiprocessing (SMP) architecture for small to medium datasets. As of today, the maximum size of an Azure SQL database must be less than 4 TB in size. Please see current on line documentation since limitations change over time. For larger data sets, Microsoft offers Azure SQL data warehouse which uses a multiple parallel processing (MPP) architecture. At this time, this service can handle up to a 240 TB database without compression and up to 1 PB database using column store technology.
Regardless of which offering you choose to use, a Database Administrator has to be notified of basic alerts. Monitoring of resources and events is key to a happy user environment. How can we enable alerts for these services?
Solution
PowerShell has always been the language of choice to automate system deployment and configuration. It is not surprising that we have a series of cmdlets to help us manage alerts using the resource manager model. Here is a MSDN reference to the cmdlets.
Business Problem
Our manager has been notified by certain remote users who can’t connect to our Azure SQL database for ad-hoc reporting. However, they have no problems accessing the database from our office. Other users are complaining that the system is very sluggish during nightly batch processing.
How can we leverage Azure alerting to discover when these problems are occurring?
Sample Azure Database
Before implementing alerting on our production database, we should design a prototype in a development region. I am going to create a sample database with sample alerts. This article assumes you know how to setup an Azure SQL database using PowerShell cmdlets or the Azure Portal. See my prior tip if you have questions.
Please create a logical Azure SQL Server named svr4tips18 in the East US region. Define the server administrator with a user name of jminer and password of MS#Sql4tips$2018. After completing this task, make a new database named db4primes using a basic tier service objective. Enclosed is the PowerShell script that I used to create the Azure objects.
If you successfully created the objects, you should see the following information when looking at the overview panel for the logical server in the Azure portal.
The purpose of this tip is to go over alerting, not database design. Therefore, I am going to supply you with a completed build script. Connect to the Azure SQL database using SQL Server Management Studio and execute the following T-SQL script within the db4primes database. The last two statements of the script return the custom objects contained within the database and the total size of the tbl_primes database. Please see image below for details.
At this point, the sample database is ready to play with.
Possible alerting rules
The Database Administrator can define alerting rules using both the Azure Portal and PowerShell. The screen shot shows us that no rules have been defined for the db4primes database.
PowerShell comes in handy when we manage 100’s databases and we might want to define 5 standard alerting rules for each database.
#
# List possible alerts
#
# Set variables
$ResourceGrp = 'rg4tips18'
$ServerNm = 'svr4tips18'
$DatabaseNm = 'db4primes'
# Grab resource id
$ResourceId = (Get-AzureRmResource -ResourceGroupName $ResourceGrp -ResourceName "$ServerNm/$DatabaseNm").ResourceID
# Get list of alerts
$AlertLst = Get-AzureRmMetricDefinition -ResourceId $ResourceId |
Select-Object @{Name="Item";Expression={$_."Name"."Value"}}, @{Name="Tag";Expression={$_."Name"."LocalizedValue"}}, @{Name="Unit";Expression={$_."Unit"}}, @{Name="Type";Expression={$_."PrimaryAggregationType"}}
# Show the sorted list
$AlertLst | Sort-Object Item
The number and characteristics of the alerts depend on the resource type. The following Azure resources have different alerts: SQL database, elastic pools and SQL data warehouse. See the MSDN documentation for more details.
If we execute the above script, we will see all the possible alerts that we can define on the db4primes database.
Please note, the call to the Get-AzureRmMetricDefinition cmdlet is retrieving the detailed information for us.
Blocked by firewall – alerting rule
The first item on our backlog list is the fact that certain users can’t connect to the Azure SQL database when they are remote. We are pretty sure this is a firewall issue. We decide to craft and test a rule for this use case.
The image above shows our one firewall rule. This screen shot was taken from firewall panel under the svr4tips18 server settings properties.
If you blindly executed the PowerShell script that I supplied, you might not have seen the code that finds your external IP address and creates a firewall rule.
Use the Azure portal to manually remove the firewall rule. The image above shows that the svr4tips18 server now has no firewall rules.
The PowerShell code below creates a new alert for blocked by firewall events. The metric value more than zero blocked users in a five minute interval is the observation we are waiting for. The alert will fire one time when the value goes above zero and it will fire again when the value goes back to zero.
# # Create alert - block by firewall # # Define constants $AlertName = "alt_db4primes_BlockByFireWall" $AlertDesc = "alert on block by firewall (cnt)" # Resource Id $ResourceId = (Get-AzureRmResource -ResourceGroupName $ResourceGrp –ResourceName "$ServerNm/$DatabaseNm").ResourceID # Email address object $ActionEmail = New-AzureRmAlertRuleEmail -CustomEmail "[email protected]" # Create alert Add-AzureRmMetricAlertRule -Name $AlertName -Location "East US 2" -ResourceGroup $ResourceGrp -TargetResourceId $ResourceId -MetricName "blocked_by_firewall" -Operator GreaterThan -Threshold 0 -WindowSize 00:05:00 -TimeAggregationOperator Total -Actions $ActionEmail -Description $AlertDesc
If we look at the Azure portal, we can see this new alert has been defined.
I suggest you insert the database name within the full name of the alert. I will tell you why this is necessary later in the tip.
Blocked by firewall – alerting notification
Try connecting to the Azure SQL database using SSMS. Make sure you use the connection properties tab to enter in db4primes as the default database. Otherwise, all blocked firewall attempts will be logged against the master database. You should get prompted to add a rule for your external IP address.
The script below uses the Get-AzureRmAlertHistory cmdlet to search for activated alerts.
# # Show the notification # $details = Get-AzureRmAlertHistory -Status Activated | Select-Object Properties, Level, EventTimeStamp $details | FL
We can see that the alert we defined was activated at 12:22 AM in the UTC time zone.
All alert activations send an email to the [email protected] address. The first message we receive is an alert activation at 7:22 PM in the EST time zone.
The alert will fire again when a full 5 minutes goes by without a blocked firewall observation. Since I stopped right away from trying to log into the system, we receive an email exactly five minutes later. The second message we receive is an alert resolution at 7:29 PM in the EST time zone.
To recap, any users on the notification list will receive an activation and resolution email pair. One problem I see with this design pattern is when a condition continues to stay above the metric and the first email is missed by the administrator. How can you tell if there is an issue?
High Database Transaction Units – alerting rule
The second item on our backlog list is the fact that certain users are experiencing slowness issues during the nightly batch process. We are pretty sure this is a resource capacity issue. Azure SQL database uses Database Transaction Units (DTUs), a blended metric of CPU, RAM and DISK I/O to express capacity. We decide to craft and test a rule for this second use case.
This code is similar in nature to the one we used before. Last time, I did not go over the two cmdlets that are at the key to this code. I will go over them now. The New-AzureRmAlertRuleEmail cmdlet is used to define one or more email address that receive notifications and the Add-AzureRmMetricAlertRule cmdlet creates a rule for a given metric name and metric value.
# # Create alert - block by firewall # # Define constants $AlertNm = "alt_db4primes_DtuPercentUsed" $AlertDesc = "Using more than 80 pct of dtu's" # Resource Id $ResourceId = (Get-AzureRmResource -ResourceGroupName $ResourceGrp –ResourceName "$ServerNm/$DatabaseNm").ResourceID # Email address object $ActionEmail = New-AzureRmAlertRuleEmail -CustomEmail "[email protected]" # Create alert Add-AzureRmMetricAlertRule -Name $AlertName -Location "East US 2" -ResourceGroup $ResourceGrp -TargetResourceId $ResourceId -MetricName "dtu_consumption_percent" -Operator GreaterThan -Threshold 80 -WindowSize 00:05:00 -TimeAggregationOperator Total -Actions $ActionEmail -Description $AlertDesc
If we take a look at the alerts for the db4primes database via the portal, you should see the two alerts we are working with.
Now that we have our second alert defined, it is time to create a test for this use case.
High Database Transaction Units – alerting notification
The nice fact about the primes database is that the algorithm is CPU intensive. We can create a batch file that calculates the prime numbers from one to a million in five different parallel stored procedure calls. This will place a lot of load on our Azure SQL database and should activate the alert.
REM REM -- Find primes numbers from 1 to 1M in parallel -- REM start "cmd" sqlcmd -S svr4tips18.database.windows.net -U jminer -P MS#Sql4tips$2018 -d db4primes -Q "EXEC active.usp_store_primes 1, 200000" start "cmd" sqlcmd -S svr4tips18.database.windows.net -U jminer -P MS#Sql4tips$2018 -d db4primes -Q "EXEC active.usp_store_primes 200001, 400000" start "cmd" sqlcmd -S svr4tips18.database.windows.net -U jminer -P MS#Sql4tips$2018 -d db4primes -Q "EXEC active.usp_store_primes 400001, 600000" start "cmd" sqlcmd -S svr4tips18.database.windows.net -U jminer -P MS#Sql4tips$2018 -d db4primes -Q "EXEC active.usp_store_primes 600001, 800000" start "cmd" sqlcmd -S svr4tips18.database.windows.net -U jminer -P MS#Sql4tips$2018 -d db4primes -Q "EXEC active.usp_store_primes 800001, 1000000"
The overview panel of the db4primes database shows that the percent DTU over time has spiked up to almost 100%.
One interesting aspect about the graph is the ability to change the parameters. If we double click on the graph, the metrics panel will show. The same metrics that we can alert on are available to the user to graph. See image below for details.
Let us check our inbox for our [email protected] email address. We can see that the second alert that we defined was activated at 08:02 PM in the EST time zone.
Since we are running 5 processes in parallel on a database that is throttled to 5 DTUs, the completion of the batch file will take some time. If this was a real production situation, we could scale up and down the resources before and after the nightly batch process. Please see this MSDN page for service tier specific information.
Last but not least, we can see that the alert we defined was resolved at 08:47 PM in the EST time zone.
In a nutshell, defining alerts for an Azure SQL database is easy. The hardest part about alerting is defining a test case that will activate the alert.
Potential Problems
Many things in life are not perfect. The alerting for Azure SQL database has some pitfalls that I will warn you about.
You can’t have the same alert name. I deployed the adventureworks database to the svr4tips18 server. I tried to generalize the name of the alert to alt_DtuPctUsed. The second call to the PowerShell cmdlet to add the alert fails with the following error message - "Can not update target resource during update". Therefore, it’s best to define alerts with database name within the alert name to guarantee uniqueness.
There might be times in which you want to disable an alert. Let’s use the Azure Portal to disable the new alert we defined for the adventure works 2014 database.
If we edit the alert, we can see two options at the top of the dialog box. We can disable or delete the alert. However, if we want to execute the same action using PowerShell, which cmdlet do we use?
The alerting cmdlets are part of the Azure Resource Module Insights module. We can call the Get-Command cmdlet and filter by source and name. The following list shows cmdlets related to alerting rules. We can see that both an email or web hook can be the target of an event.
One would think there would be a Set-AzureRmMetricAlertRule cmdlet that could be used to set the properties of the alert such as enable and disable. However, Microsoft has not supplied us with such an interface.
If you look real hard at the documentation, you will notice that the add cmdlet adds a new rule if it does not exist or updates a current rule if one does. There is a DisableAlert parameter that can be used to satisfy our business requirement.
Summary
Today, we explored how to create alerts using either the Azure Portal or PowerShell cmdlets. Microsoft supplies the administrator with 15 metrics that you can either alert on or graph over time. If you are managing more than a handful of databases, PowerShell is the only way to go.
There are some things to remember when using alerts.
An activation email is only sent one time to the recipient address. If the metric has been exceeded for one hour straight, you still will only have one email. When the metric is returned to normal, a resolved email will be issued. The observation time period is between 5 minutes and 1 day.
The name of the alert has to be unique. I know this is a requirement for multiple databases associated with one logical server. I have not researched if multiple servers can have the same alert name. I leave this research for you to complete.
The add alert cmdlet uses an UPSERT algorithm. If the alert does not exist, it adds the alert. If the alert does exist, it updates the alert. I really do have to complain about the naming of this cmdlet. Even Microsoft has a standard for naming conventions that is not being followed in this case. I suggest another alias should be defined in the supplied module, to allow for a set operation, by calling the existing add cmdlet.
In a nutshell, the alerting rules can be your first line of defense for Azure SQL database, elastic pools and Azure SQL data warehouse.
Next Steps
- Enabling diagnostics settings to collect useful information
- Monitoring Azure SQL database with Microsoft Management Suite
About the author
John Miner is a Data Architect at Insight Digital Innovation helping corporations solve their business needs with various data platform solutions.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-02-16
