How to Create an Azure Alert using Powershell for SQL Database?

Question

This doesn't fit for Azure SQL Database or Managed Instance.

https://learn.microsoft.com/en-us/powershell/module/az.monitor/add-azmetricalertrulev2?view=azps-6.3.0

and this method is apparently out dated and no longer valid.

https://www.mikefal.net/2016/08/23/creating-alerts-for-azure-sql-database-with-powershell/

Does anyone know how to create an alert using the new syntax for Azure SQL Database? One the specifies severity...etc. like the V2 in the first link?

Answer 1

The following script creates DTU alert rules that will monitor for the DTU going above 90 % for the last 15min. It will create alerts all databases within a resource group.

#define variable for resource group name by requesting keyboard input

$rg = Read-Host 'Please, input resource group name here (exactly as it is in Azure)'

<#create the array containing databases where alerts are required. The value of v12.0,user corresponds to the kind of resource as to include only the SQL DBs and not the SQL servers#>

$resources = Get-AzureRmResource | ?{ $_.ResourceGroupName -eq $rg -and $_.kind -eq "v12.0,user"  } | select resourcename,resourceid

#loop through the array and create the alert rule for each DB

foreach($resource in $resources){$alertname=$resource.resourcename.Substring($resource.resourcename.IndexOf('/')+1);Add-AzureRMMetricAlertRule -ResourceGroup $rg -location "centralus" -targetresourceid $resource.resourceid -Name $alertname -MetricName "dtu_consumption_percent" -Operator "GreaterThan" -Threshold 90 -WindowSize $([TimeSpan]::Parse("00:15:00")) -TimeAggregationOperator "Average" -verbose -Actions $(New-AzureRmAlertRuleEmail -SendToServiceOwners -CustomEmails "Client-address@domain.com")}

You can also find a complete example using Add-AzMetricAlertRuleV2 on this article.

Answer 2

@Alberto Morillo - I got it working. Here is the iteration I am on. I say that because I still have adjustments to make, but this creates an alert with multiple Action Groups.126124-set-azsqldb-alerts.txt

Answer 3

OK, so I managed to create the alert! Thank you! Now I have another question. If the action group and the alert do not exist, this works fine, but if it exists, it fails saying the ENgActGrp value is NULL or Empty. How is that even remotely possible when it says it found it and populated the variable?

 $ResourceType = "Microsoft.Sql/servers/databases"

            <#
                Action Group "Database Engineering Team"
            #>
            $EngActGrp = Get-AzActionGroup -ResourceGroupName $ResourceGroup | Select-Object Name | Where-Object {$_.Name -eq "Database Engineering Team"}

            if (!$EngActGrp)
            {
                Write-Host "Not Found"
                $EmailReciever = New-AzActionGroupReceiver -Name "Database Engineering Team" -EmailReceiver -EmailAddress "MyEMail@MyWork.com"

                $EngActGrp = Set-AzActionGroup -Name "Database Engineering Team" -ShortName "Db Eng Team" -ResourceGroupName $ResourceGroup -Receiver $EmailReciever
            }
            else
            {
                Write-Host "Found"
            }

            $EngActGrpId = New-AzActionGroup -ActionGroupId $EngActGrp.Id


            <#
                Alert Rule "DTU > 90%"
            #>
            $MarDTUPct = Get-AzMetricAlertRuleV2 -ResourceGroupName $ResourceGroup -Name "DBA-MAR-DTU-GT90"

            if (!$MarDTUPct)
            {
                $DTUPctCondition = New-AzMetricAlertRuleV2Criteria -MetricName "dtu_consumption_percent" -MetricNameSpace $ResourceType -TimeAggregation Average `
                                                                   -Operator GreaterThan -Threshold 90

                $TargetResourceId = (Get-AzResource -ResourceGroupName $ResourceGroup -ResourceType $ResourceType -Name $Database).ResourceId

                Add-AzMetricAlertRuleV2 -Name "DBA-MAR-DTU-GT90" -ResourceGroupName $ResourceGroup -WindowSize 00:15:00 -Frequency 00:01:00 -TargetResourceScope $TargetResourceId `
                                        -TargetResourceType $ResourceType -TargetResourceRegion $Region -Description "DBA Avg DTU > 90%" -Severity 2 -Condition $DTUPctCondition `
                                        -ActionGroup $EngActGrpId
            }