Email Notifications for Azure SQL Database

Hi Mates,

In this article we are going to explore how to send email notifications for Azure SQL Database.

As we know being DBA's we heavily rely on Alert notification to be more pro-active hence we are going to explore the alerting mechanism for SQL Azure Database.

Given below are the list of metrics which are available in Azure SQL.

For today's discussion I picked up setting an alert for Deadlock. Deadlocks are very common issues that DBA's need to work on. I am not going to describe how to generate a deadlock as there are many articles available on the internet. We are just going to see how to create an alert in place for Deadlocks.

We will be achieving this using Powershell & here are the steps. In my previous post I shown you how to install Azure related powershell cmdlets. Once that's done Just check whether you are able to find Azure related alerts by firing Get-Command *Azure*Alerts*

As you can see I do have the relevant cmdlets to proceed further.

Because these cmdlets are generic, we have to identify the specific metrics we can monitor for Azure SQL Database. Fortunately, these are discoverable using the Get-AzureRmMetricDefinition, which is not listed but part of the AzureRM.Insights module.To use it, we need to know our Azure resource ID, which we can derive from three pieces of information: the resource group, the server name, and the database name which I populated by passing the info of mine.

$ResourceGroup = 'kirshnares'
$server = 'krishnainstance'
$db = 'deadlocks'
$rid = (Get-AzureRmResource -ResourceGroupName $ResourceGroup -ResourceName "$server/$db").ResourceID
Get-AzureRmMetricDefinition -ResourceId $rid | Format-Table

Creating an Alert

So let’s get down to brass tacks and actually create an alert. To do this, we need some info first:
The Resource Group we will create the alert in.
An Azure location where the alert will live.
An Azure SQL Database server and database we are creating the alert for.
What metric we will monitor and what is the threshold we will be checking.
(optional) An email to send an alert to.

With this, here’s the settings I’m going with:
Resource Group – kirshnares
Location – South India
Server – krishnainstance
Database – Deadlocks
Alert – Deadlock greater than or equal to 1

There are a couple other considerations for creating an alert. The first is a time window
the alert will check against. This window can be anywhere from five minutes to a full day.
The second is what aggregation of the metric we will check (i.e. total, average, maximum).
You’ll want to use values and aggregations that make sense, but for the example alert, we’ll
check the maximum value over a 5 minute window.

Once we have everything in place, we will call the New-AzureRmAlertRuleEmail cmdlet to create our email notification and Add-AzureRmMetricAlertRule cmdlet to create 

our alert below is the script:

$ResourceGroup = 'kirshnares'
$location = 'South India'
$server = 'krishnainstance'
$db = 'deadlocks'
$rid = (Get-AzureRmResource -ResourceGroupName $ResourceGroup -ResourceName "$server/$db").ResourceID
$email = New-AzureRmAlertRuleEmail -CustomEmails 'urskrishna.kethireddy"outlook.com' -SendToServiceOwners
Add-AzureRmMetricAlertRule -Name 'Deadlock' -Location $location -ResourceGroup $ResourceGroup -TargetResourceId $rid -MetricName 'deadlock' -Operator GreaterThanOrEqual -Threshold 1 -WindowSize '00:05:00' -TimeAggregationOperator Maximum -Actions $email

And now we can look at our shiny new alert

you can explore the other metric alerts by choosing them Accordingly & see how it helps you in your environments. 

Until then Happy Reading.