Microsoft Azure Documentation

Manage Alerts for Azure SQL Database

This tutorial guides you through setting up and managing alerts for your Azure SQL Database. Alerts are crucial for proactive monitoring, allowing you to respond quickly to potential issues before they impact your users.

Setting Up Diagnostic Settings

Before you can create alerts based on logs, you need to configure diagnostic settings to send your database logs to a destination like a Log Analytics workspace.

1. Navigate to your Azure SQL Database resource in the Azure portal.
2. In the left-hand menu, under the Monitoring section, select Diagnostic settings.
3. Click Add diagnostic setting.
4. Select the logs and metrics you want to collect. For alerts, consider SQLInsights.QueryStoreRuntimeStats_CL, SQLInsights.Availability_CL, and relevant performance metrics.
5. Choose a destination for these settings, such as sending to a Log Analytics workspace.
6. Click Save.

Creating Metric Alerts

Metric alerts are triggered when a specific metric breaches a defined threshold. Common metrics to monitor include:

• CPU Percentage: Indicates the CPU utilization of the database.
• Data IO Percentage: Monitors data read/write operations.
• Log IO Percentage: Tracks log write operations.
• DTU Percentage: For DTU-based purchasing models, this shows the percentage of Database Transaction Units used.
• Storage Percentage: Monitors the used storage space.

Steps to Create a Metric Alert:

1. In the Azure portal, go to Monitor > Alerts.
2. Click Create > Alert rule.
3. Under Scope, select your Azure SQL Database.
4. Click Add condition.
5. In the Signal name search box, type the metric you want to monitor (e.g., "CPU Percentage").
6. Configure the Alert logic:
   • Threshold type: Static or Dynamic.
   • Operator: Greater than, Less than, etc.
   • Aggregation type: Average, Maximum, Minimum.
   • Threshold value: The value that triggers the alert.
   • Evaluation based on: Observe, Count.
7. Click Done.
8. Under Actions, select or create an Action group to define what happens when the alert fires (e.g., send an email, trigger an automation runbook).
9. Provide an Alert rule name, Description, and select the Severity.
10. Click Review + create, then Create.

Creating Log Alerts

Log alerts allow you to run a Log Analytics query at a specified frequency and trigger an alert if the query results exceed a certain number of rows.

Steps to Create a Log Alert:

1. In the Azure portal, go to Monitor > Alerts.
2. Click Create > Alert rule.
3. Under Scope, select your Azure SQL Database.
4. Click Add condition.
5. Select the Custom log search signal.
6. In the Search query box, enter your Kusto Query Language (KQL) query. For example, to alert on slow-running queries impacting performance:

   AzureDiagnostics
   | where ResourceType == "SQLSERVERS/DATABASES"
   | where Category == "QueryStoreRuntimeStats"
   | where duration_s > 30 // Alert if any query runs longer than 30 seconds
   | project TimeGenerated, database_name_s, query_s, duration_s, batch_sql_handle_s
   

7. Configure the Alert logic:
   • Measure based on: Number of results.
   • Operator: Greater than.
   • Threshold value: Set to 0 to trigger on any matching query.
   • Frequency: How often the query runs (e.g., every 5 minutes).
   • Period: The time window for the query (e.g., 5 minutes).
8. Click Done.
9. Configure Actions, Alert rule name, Description, and Severity as described for metric alerts.
10. Click Review + create, then Create.

Managing Action Groups

Action groups are a key component of alerts, defining the set of actions to take when an alert fires. These actions can include:

• Sending notifications (email, SMS, push notifications, voice calls).
• Triggering automated actions (Azure Functions, Logic Apps, Webhooks, Automation Runbooks).

You can create and manage action groups separately under the Monitor service in the Azure portal.

Best Practices for Alerts

• Start with critical metrics: Focus on resource utilization, error rates, and availability.
• Set meaningful thresholds: Avoid overly sensitive alerts that lead to alert fatigue. Use dynamic thresholds where appropriate.
• Define clear actions: Ensure that each alert has a well-defined response plan.
• Group alerts logically: Use severity levels to prioritize responses.
• Regularly review and tune: As your database workload evolves, so should your alerting strategy.