Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



Create Alert for Azure SQL Database using Powershell Code
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-08-24








Solution:



Step #1: Authenticate to Azure

Login-AzureRmAccount


Step #2: Set your Azure Subscription

#Set the Azure Subscription
$Subscription = "SubDev"


Step #3: Set whether to Create or Delete

$DeleteNewMetricAlertRules = "0" #1=Delete


Step #4: Run below Powershell Code


If ($Subscription -eq "SubDev")
{
Write-Output 'Deploying to Azure SubDev Subscription...'

#Select your Azure Subscription
Select-AzureRmSubscription -SubscriptionName "SubDev"

#Define Variables: SubDev
$ResourceGroup = 'InsertRGNameHere'
$Location = 'InsertAzureLocationHere'
$Server = 'InsertServerNameHere'
$DB = 'InsertDBNameHere'
}

If ($Subscription -eq "SubTst")
{
Write-Output 'Deploying to Azure SubTst Subscription...'

#Select your Azure Subscription
Select-AzureRmSubscription -SubscriptionName "SubTst"

#Define Variables: SubTst
$ResourceGroup = 'InsertRGNameHere'
$Location = 'InsertAzureLocationHere'
$Server = 'InsertServerNameHere'
$DB = 'InsertDBNameHere'
}

If ($Subscription -eq "SubPrd")
{
Write-Output 'Deploying to Azure SubPrd Subscription...'

#Select your Azure Subscription
Select-AzureRmSubscription -SubscriptionName "SubPrd"

#Define Variables: SubPrd
$ResourceGroup = 'InsertRGNameHere'
$Location = 'InsertAzureLocationHere'
$Server = 'InsertServerNameHere'
$DB = 'InsertDBNameHere'
}

#Get Azure SQL Server
$ServerObject = Get-AzureRMSQLServer -ResourceGroupName $ResourceGroup | Where-Object {$_.ServerName -eq $Server}

If ($ServerObject.ServerName -eq $Server)
{

Write-Output 'Azure SQL Server exists = ok'

#Get Azure SQL Database
$DBObject = Get-AzureRmSqlDatabase -ResourceGroupName $ResourceGroup -ServerName $ServerObject.ServerName | Where-Object {$_.DatabaseName -eq $DB}

If ($DBObject.DatabaseName -eq $DB)
{

Write-Output 'Azure SQL Database exists = ok'

#Get Azure ResourceID
$ResourceID = (Get-AzureRmResource -ResourceGroupName $ResourceGroup -ResourceName "$Server/$DB").ResourceID

#Define Alert Email
$AlertEmail = New-AzureRmAlertRuleEmail -CustomEmails 'alerts@domainxyz.ca'


If ($DeleteNewMetricAlertRules -eq "1")
{
#Remove New Metric Alert Rule
Write-Output 'Deleting New Metric Alert Rule(s) request sent..'
Remove-AzureRmAlertRule -ResourceGroup $ResourceGroup -Name "DTU High"
Remove-AzureRmAlertRule -ResourceGroup $ResourceGroup -Name "CPU High"
Remove-AzureRmAlertRule -ResourceGroup $ResourceGroup -Name "Connection Failed High"
Remove-AzureRmAlertRule -ResourceGroup $ResourceGroup -Name "Connection Succeeded High"
Write-Output 'Azure New Metric Rules(s) deleted = ok'
}
Else
{
#Create New Metric Alert Rule
Write-Output 'Azure New Metric Alert Rule(s) request sent..'

Add-AzureRmMetricAlertRule -Name "DTU High" -ResourceGroup $ResourceGroup -Location $Location -TargetResourceId $ResourceID -MetricName "dtu_consumption_percent" -Operator GreaterThan -Threshold 90 -WindowSize 00:05:00 -TimeAggregationOperator Maximum -Actions $AlertEmail
Add-AzureRmMetricAlertRule -Name "CPU High" -ResourceGroup $ResourceGroup -Location $Location -TargetResourceId $ResourceID -MetricName "cpu_percent" -Operator GreaterThan -Threshold 90 -WindowSize 00:05:00 -TimeAggregationOperator Maximum -Actions $AlertEmail
Add-AzureRmMetricAlertRule -Name "Connection Failed High" -ResourceGroup $ResourceGroup -Location $Location -TargetResourceId $ResourceID -MetricName "connection_failed" -Operator GreaterThan -Threshold 0 -WindowSize 00:05:00 -TimeAggregationOperator Maximum -Actions $AlertEmail
Add-AzureRmMetricAlertRule -Name "Connection Succeeded High" -ResourceGroup $ResourceGroup -Location $Location -TargetResourceId $ResourceID -MetricName "connection_successful" -Operator GreaterThan -Threshold 25 -WindowSize 00:05:00 -TimeAggregationOperator Maximum -Actions $AlertEmail

#$AlertRule = Get-AzureRmAlertRule -ResourceGroup $ResourceGroup | Select-Object -Property Name | Format-List
#$AlertRule
Write-Output 'Azure New Metric Rules(s) created = ok'
}
}
Else
{
$MSGDB = "Azure SQL Database does not exist!"
$MSGDB
}

}
Else
{
$MSGServer = "Azure SQL Server does not exist!"
$MSGServer
}




Image #1:





#Azure PS Helper:

#Get-Command *Azure*Alert*

#Get-AzureRmMetricDefinition -ResourceId $ResourceID | Format-Table

#Get-AzureRmAlertRule -ResourceGroup $ResourceGroup



Resources:

Microsoft Azure

Add-AzureRmMetricAlertRule