LLI Wiki — My Digital Knowledge Base

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

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

Testing an alert

  1. In dev, temporarily lower the threshold through the parameter file and deploy.
  2. Use the portal's "Test action group" feature to validate the notification channel.
  3. Run the KQL query alone in Log Analytics before wiring it in.
  4. Check that a processing rule is not masking your test (scope and schedule).

Lessons learned

Mastodon LLIMastodon SocialFosstodon