Create an Azure SQL Database Alert Rule using Azure CLI

28 December 21

In Azure, we can provision SQL Databases with a DTU (Database Transaction Unit) based purchase model, where the amount of DTUs determines the compute power available to the database resource (amongst other metrics).

The amount of DTUs available can influence how well an application can perform given its workload. A higher percentage consumption of the available DTUs by an application process can lead to increased processing times as operations against the database take longer to complete. Where we find DTU consumption is maxing out or running close to the limit, we have options available such as

• Reviewing the database commands our application is executing to see if code could be optimized
• Applying indexes to the database schema where appropriate
• Upgrade the Azure resource to increase the DTUs available

We can create Alert Rules to monitor our databases DTU consumption. Let's take a look at how to do that with the Azure CLI. In the Azure portal, any Alert Rules configured can be viewed from both the resource they are created for as well as from Azure Monitor

Let's say we want an Alert Rule that checks the maximum DTU consumption of our database over the last hour, every hour, and will alert us if the value is greater than 90%. It's important to point out that when an alert condition is met it will trigger an action. An action is an event that can be configured such as sending an email / SMS / call to a specified recipient(s), triggering an application within Azure such as a Logic App or Function, making a request to a webhook etc. Actions are then grouped into an Action Group. Configuring Action Groups are not within the scope of this article, however we will need its resource id that we can use the Azure CLI Action Group show command to get hold of

az monitor action-group show \
  --name "${actionName}" \
  --resource-group "${actionResourceGroup}" \
  --output "tsv" \
  --query "id"

Note that ${actionName} and ${actionResourceGroup} are variables that have been set as part of a shell script where this code is copied from - string values can be used in their place. For more detail of the query output see https://docs.microsoft.com/en-us/cli/azure/format-output-azure-cli. Copy the output value, we will use it as ${actionGroupId} when creating the Alert Rule later

Additionally, we will also need to get the id of our SQL database using the Azure CLI SQL DB show command. We need to set the values for ${dbName}, ${dbResourceGroup} and ${dbServer} and grab the command output to use below as ${dbId}

az sql db show \
  --name "${dbName}" \
  --resource-group "${dbResourceGroup}" \
  --output "tsv" \
  --query "id" \
  --server "${dbServer}"

Now we can use the output values to create our alert using the Azure CLI Monitor Metrics Alert create command

az monitor metrics alert create \
    --condition "max dtu_consumption_percent > 90" \
    --name "${dbAlertRuleName}" \
    --resource-group "${dbResourceGroup}" \
    --scopes "${dbId}" \
    --action "${actionGroupId}" \
    --auto-mitigate true \
    --description "Max DTUs consumption was greater than 90% in the last hour." \
    --evaluation-frequency 1h \
    --severity 2 \
    --window-size 1h

As noted above the variable values have been predefined in a wider script, they can be replaced simply with strings. A JSON response will display in the terminal on success of this command and after a short time the newly created Alert Rule will be visible in the Azure portal