Azure Monitor alerts as code for Azure SQL and Azure Batch
Define metric and log search alerts in versioned Bicep files, deploy them with a pipeline, handle maintenance windows and avoid alert fatigue.
An alert created by hand in the portal is an alert nobody reviews, nobody can compare between environments, and that is lost at the first subscription change. To monitor Azure SQL (databases and elastic pools) and Azure Batch, I define every alert in versioned files and deploy them with a pipeline. Here is how I do it.
Overview
flowchart LR
A["Git (Bicep / JSON files)"] --> B["Pull request + review"]
B --> C["Pipeline (what-if then deploy)"]
C --> D["Azure Monitor : alert rules"]
D --> E["Alert fired"]
E --> F["Processing rule (maintenance?)"]
F --> G["Action group"]
G --> H["On-call person"]
The key point: the alert processing rule (maintenance) sits after the alert fires and before the notification. The alert still exists in Azure Monitor; only the notification is suppressed.
Metric alert or log search alert?
| Metric alert | Log search (scheduled query) alert | |
|---|---|---|
| Source | Platform metrics | KQL query on Log Analytics |
| Latency | Low, evaluated every minute | Higher (ingestion + frequency) |
| Flexibility | Threshold on a metric, dimensions | Joins, computations, anything KQL allows |
| Resource type | Microsoft.Insights/metricAlerts |
Microsoft.Insights/scheduledQueryRules |
My rule: metric first (CPU, sessions, pool storage, Batch task failures), a KQL query only when the metric is not enough (custom aggregation, correlating several signals).
An action group
resource ag 'Microsoft.Insights/actionGroups@2023-01-01' = {
name: 'ag-dba-oncall'
location: 'global'
properties: {
groupShortName: 'dba-oncall'
enabled: true
emailReceivers: [
{ name: 'oncall', emailAddress: 'dba@contoso.com', useCommonAlertSchema: true }
]
}
}
One action group per escalation level, never one per alert.
Metric alert on an elastic pool
param poolId string
param actionGroupId string
resource cpu 'Microsoft.Insights/metricAlerts@2018-03-01' = {
name: 'sql-elp-cpu-warning'
location: 'global'
properties: {
description: 'Elastic pool CPU above threshold'
severity: 2
enabled: true
scopes: [ poolId ]
evaluationFrequency: 'PT5M'
windowSize: 'PT15M'
autoMitigate: true
criteria: {
'odata.type': 'Microsoft.Azure.Monitor.SingleResourceMultipleMetricCriteria'
allOf: [
{
criterionType: 'StaticThresholdCriterion'
name: 'cpu'
metricNamespace: 'Microsoft.Sql/servers/elasticPools'
metricName: 'cpu_percent'
operator: 'GreaterThan'
threshold: 80
timeAggregation: 'Average'
}
]
}
actions: [ { actionGroupId: actionGroupId } ]
}
}
For Batch, the TaskFailEvent metric (Total, dimensions poolId and jobId) of the Batch account is used the same way with a Total aggregation.
Log search alert (KQL)
resource deadlocks 'Microsoft.Insights/scheduledQueryRules@2022-06-15' = {
name: 'sql-deadlock-warning'
location: location
kind: 'LogAlert'
properties: {
displayName: 'SQL deadlocks'
severity: 3
enabled: true
scopes: [ workspaceId ]
evaluationFrequency: 'PT15M'
windowSize: 'PT15M'
autoMitigate: true
muteActionsDuration: 'PT1H'
criteria: {
allOf: [
{
query: 'AzureMetrics | where ResourceProvider == "MICROSOFT.SQL" and MetricName == "deadlock" | where Total > 0'
timeAggregation: 'Count'
operator: 'GreaterThan'
threshold: 3
failingPeriods: { numberOfEvaluationPeriods: 1, minFailingPeriodsToAlert: 1 }
}
]
}
actions: { actionGroups: [ actionGroupId ] }
}
}
Maintenance windows: processing rule
Disabling alerts during maintenance is fragile: people forget to re-enable them. An alert processing rule (Microsoft.AlertsManagement/actionRules) suppresses notifications on a schedule:
resource quiet 'Microsoft.AlertsManagement/actionRules@2021-08-08' = {
name: 'apr-maintenance-weekly'
location: 'global'
properties: {
scopes: [ resourceGroup().id ]
enabled: true
actions: [ { actionType: 'RemoveAllActionGroups' } ]
schedule: {
timeZone: 'Romance Standard Time'
recurrences: [
{ recurrenceType: 'Weekly', daysOfWeek: [ 'Sunday' ], startTime: '02:00:00', endTime: '05:00:00' }
]
}
}
}
Naming and severity conventions
- Name:
<domain>-<scope>-<signal>-<level>, for examplesql-elp-cpu-warning. A predictable name can be filtered and targeted by processing rules. - Severity: 0 critical and 1 error wake someone up; 2 warning and 3 informational go to a channel, never to a phone at night.
- Tags: environment and owner on every rule.
Pipeline deployment
steps:
- task: AzureCLI@2
displayName: What-if
inputs:
azureSubscription: 'sc-dba-monitoring'
scriptType: 'pscore'
scriptLocation: 'inlineScript'
inlineScript: |
az deployment group what-if -g rg-dba-prd -f alerts/main.bicep -p alerts/prd.bicepparam
- task: AzureCLI@2
displayName: Deploy
condition: eq(variables['Build.SourceBranch'], 'refs/heads/main')
inputs:
azureSubscription: 'sc-dba-monitoring'
scriptType: 'pscore'
scriptLocation: 'inlineScript'
inlineScript: |
az deployment group create -g rg-dba-prd -f alerts/main.bicep -p alerts/prd.bicepparam
What-if runs on every pull request, the deployment only on main.
Avoiding alert fatigue
- Window and failing periods: a 5-minute spike is not an incident. Evaluate every 5 minutes over 15, or require several failing periods.
- Frequency matched to the signal: evaluating every minute a metric that moves over hours costs money and adds noise.
autoMitigate: true: the alert resolves itself when the signal returns to normal, so nobody closes alerts by hand.muteActionsDurationon log alerts: one notification per period.- Two levels (warning, critical) on the same signal with different thresholds, rather than a single threshold.
Testing an alert
- In dev, temporarily lower the threshold through the parameter file and deploy.
- Use the portal's "Test action group" feature to validate the notification channel.
- Run the KQL query alone in Log Analytics before wiring it in.
- Check that a processing rule is not masking your test (scope and schedule).
Lessons learned
- Processing rules replaced all my enable/disable scripts: far less risk of forgetting to re-enable.
- One file per alert or one parameterised module: code review shows at once that a threshold changed.
- Pitfall: a log search alert has ingestion delay; never set its window shorter than that delay.
- Pitfall: the same name on two different scopes silently overwrites one of the rules; put the scope in the name.
- Pitfall: check the API version on Microsoft Learn before copying a sample; properties evolve.