<# .NAME Set-AzSqlDb-Alerts .SYNOPSIS Configures alerts for Azure SQL Datbase. .DESCRIPTION Connects to the specified Azure SQL Database and configure default alerts. .PARAMETER Resource Group Name of the Azure Resource Group containing the Azure SQL Database. .PARAMETER Region Name of the Region where the Azure SQL Database resides. .PARAMETER Server Name of the server container where the Azure SQL Datbase exists. .PARAMETER Azure SQL Database Name of the Azure SQL Database. .EXAMPLE Set-AzSqlDb-Alerts -ResourceGroup "Your-Resource-Group" -Region "West US" -Server "Your-Server-Name" -Database "Your-Database-Name" .NOTES v1.0 - 08/13/2021 - Default list of Alerts Alert Condition & Rule Values A. Time Aggregation Values (Average, Minimum, Maximum, Total) B. Operator Values (Equals, NotEquals, GreaterThan, GreaterThanOrEqual, LessThan, LessThanOrEqual) C. Severity values (0 - Critical, 1 - Error, 2 - Warning, 3 - Informational, 4 - Verbose) Azure Resource Types A. Azure SQL Database = Microsoft.Sql/servers/databases Alert Rules A. DTUs 1. Warning > 70%, Critical > 90% 2. Evaluated Every 5 minutes 3. Duration > 30 minutes B. CPU 1. Warning > 70%, Critical > 90% 2. Evaluated Every 5 minutes 3. Duration > 30 minutes C. * #> function Set-AzSqlDb-Alerts { [CmdletBinding()] param ( [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 0)] $ResourceGroup, [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 1)] $Region, [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 2)] $Server, [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 3)] $Database ) process { try { $TeamActionGroupID = Reset-AzSqlDb-ActionGroup -ResourceGroup $ResourceGroup -ActionGroupName "Database Engineering Team" $SlackActionGroupID = Reset-AzSqlDb-ActionGroup -ResourceGroup $ResourceGroup -ActionGroupName "Database Slack Alerts" $VictorOpsActionGroupID = Reset-AzSqlDb-ActionGroup -ResourceGroup $ResourceGroup -ActionGroupName "Victor Ops Alerts" $RuleDTUGT90 = "DBA-MAR-$($Server)-$($Database)-DTU-GT90" Reset-AzSqlDb-Alert -ResourceGroup $ResourceGroup -Region $Region -Database $Database -Rule $RuleDTUGT90 -Metric "dtu_consumption_percent"` -Operator "GreaterThan" -Aggregation "Average" -Threshold 90 -ActionGroupOne $TeamActionGroupID -ActionGroupTwo $SlackActionGroupID } catch [system.exception] { throw $_ } } } function Reset-AzSqlDb-Alert { [CmdletBinding()] param ( [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 0)] $ResourceGroup, [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 1)] $Region, [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 2)] $Database, [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 3)] $Rule, [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 4)] $Metric, [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 5)] $Operator, [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 6)] $Aggregation, [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 7)] $Threshold, [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 8)] $ActionGroupOne, [Parameter(Mandatory = $false, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 9)] $ActionGroupTwo ) process { try { $ResourceType = "Microsoft.Sql/servers/databases" Write-Host "Resetting Alert Rule '$($Rule)'." -ForegroundColor Green if (Get-AzMetricAlertRuleV2 -ResourceGroupName $ResourceGroup -Name $Rule -ErrorAction SilentlyContinue) { Write-Host "Removing existing Alert Rule." -ForegroundColor Green Remove-AzMetricAlertRuleV2 -ResourceGroupName $ResourceGroup -Name $Rule } else { Write-Host "Alert Rule Not Found." -ForegroundColor Green } Write-Host "Creating Alert Rule Criteria." -ForegroundColor Green $Condition = New-AzMetricAlertRuleV2Criteria -MetricName $Metric -MetricNameSpace $ResourceType -TimeAggregation $Aggregation ` -Operator $Operator -Threshold $Threshold Write-Host "Grabbing Resource Id." -ForegroundColor Green $TargetResourceId = (Get-AzResource -ResourceGroupName $ResourceGroup -ResourceType $ResourceType -Name $Database).ResourceId Add-AzMetricAlertRuleV2 -Name $Rule -ResourceGroupName $ResourceGroup -WindowSize 00:15:00 -Frequency 00:01:00 -TargetResourceScope $TargetResourceId ` -TargetResourceType $ResourceType -TargetResourceRegion $Region -Severity 2 -Condition $Condition ` -ActionGroupId $ActionGroupOne.ActionGroupId,$ActionGroupTwo.ActionGroupId } catch [system.exception] { throw $_ } } } function Reset-AzSqlDb-ActionGroup { [CmdletBinding()] param ( [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 0)] $ResourceGroup, [Parameter(Mandatory = $true, ValueFromPipeLine = $true, ValueFromPipeLineByPropertyName = $true, Position = 1)] $ActionGroupName ) process { try { Write-Host "Creating Action Group $($ActionGroupName)" -ForegroundColor Green if (Get-AzActionGroup -ResourceGroupName $ResourceGroup -Name $ActionGroupName -ErrorAction SilentlyContinue) { Write-Host "Action Group Found." -ForegroundColor Red Remove-AzActionGroup -ResourceGroupName $ResourceGroup -Name $ActionGroupName } else { Write-Host "Action Group Not Found." -ForegroundColor Green } Write-Host "Creating Action Group." -ForegroundColor Green $ActionGroupReceiver = New-AzActionGroupReceiver -Name $ActionGroupName -EmailReceiver -EmailAddress "Your_Email_Address" Set-AzActionGroup -Name $ActionGroupName -ShortName "Database" -ResourceGroupName $ResourceGroup -Receiver $ActionGroupReceiver $ActionGroup = Get-AzActionGroup -Name $ActionGroupName -ResourceGroupName $ResourceGroup $ActionGroupId = New-AzActionGroup -ActionGroupId $ActionGroup.Id } catch [system.exception] { throw $_ } return $ActionGroupId } }