Setup Alert for Azure SQL DB database status

A few days ago, I was working on a customer case where their database went into inaccessible state due to Server level CMK TDE got expired.

Though the database was brought back to online, customer wanted to know how they can set alert and get notified when the database status changes from online

In this post, I want to share the steps to set an email alert when the database status becomes inaccessible. The same steps can be used to get notified for any different database status.

Create alerts with Azure Resource Graph and Log Analytics

You can use queries to set up alerts for your deployed Azure resources. You can create queries using Azure Resource Graph tables.

Azure Resource Graph: Uses the Azure Resource Graph Resources table to create a query that gets data for your deployed Azure resources and create an alert.

Create workspace:

Create a Log Analytics Workspace in the subscription that's being monitored.

If you used Log Analytics workspaces, you could select it from Azure services.

Create query:

From the Log Analytics workspace, create an Azure Resource Graph query to get a count of your Azure resources. This example uses the Azure Resource Graph Resources table.

1. Select Logs from the left side of the Log Analytics workspace page. Close the Queries window if displayed.
2. Use the following code in the New Query:

arg("").Resources

| where type =~ "microsoft.sql/servers/databases"

| where tostring(properties.status) == "Inaccessible"

Create alert rule:

From the Log Analytics workspace, select New alert rule. The query from your Log Analytics workspace is copied to the alert rule. Create an alert rule has several tabs that need to be updated to create the alert.

Scope:

Verify that the scope defaults to your sql databases under your subscription.

Condition:

The form has several fields to complete but make sure you set 2 fields correctly Under Alert logic to generate alerts:

• Operator: Greater than
• Threshold value: Use a number that's less that the number returned from the query. Logically you like to get notified whenever a single DB goes inaccessible, so we can set the value as 0.
• Frequency of evaluation: 5 minutes

Select Next: Actions.

Actions:

Select or create a new action group.

Details:

Fill all the fields under the Details tab and make sure to set the identity correctly.

• Identity: Select System assigned managed identity.
  
  

Select Review + create, verify the summary is correct, and select Create.

Assign role:

It is important to assign the system-assigned managed identity with Log Analytics Reader permission under your subscriptions scope so that it has permissions to fire alerts that send email notifications.

1. Select Monitoring > Alerts 
2. Select Alert rules and the newly created alert rule
3. Select Settings > Identity > System assigned:
   
   

    

   
   
   • Status: On
   • Object ID: Shows the GUID for your Enterprise Application (service principal) in Microsoft Entra ID.
   • Permission: Select Azure role assignments:
     • Verify your subscription is selected.
     • Select Add role assignment:
     • Scope: Subscription
     • Subscription: Select your Azure subscription name.
     • Role: Log Analytics Reader
       
       

        

4. Select Save.

It takes a few minutes for the Log Analytics Reader to display on the Azure role assignments page. Select Refresh to update the page.

After the role is assigned to your alert rule, you begin to receive email for alert messages.

Reference: How Azure Resource Graph uses alerts to monitor resources - Azure Resource Graph | Microsoft Learn