azureazure-alertssql-azure-alerts

Subscription wide Azure Alert with SQL Script to run in the database from which it was triggered

I'm asking this question because I am getting lost in the woods, and I am afraid that I might end up implementing something that just isn't right, so what I'm looking for is a step by step explanation, explained to me "like I'm 5" because with regard to this side of Azure, I pretty much am.

I am currently looking at setting up an Azure Alert. I have no problem setting one up for a single Azure SQL Server database, triggering the Alert whenever the DTU % on that database runs at an average of 95% for 10 minutes or more.

But we have several hundred Azure databases.... I would love to be able to set up a single alert that is triggered whenever this happens on any one of these databases. I would like to avoid having to configure that same alert over and over and over again, once for each database.

Assuming that this is even possible.... the next thing I would need to do is run a SQL statement against the database that triggered the Alert.

Any tips welcome. Apologies in advance as I'm still at that awkward "I don't even know how to start" stage. It's awful when you're SO ignorant that you don't even know how to ask the right question.

Solution

  • To set up an Azure Alert for multiple Azure SQL Server databases, you can use Azure Monitor. Repeat the steps for all the SQL databases you want to monitor.

    You can create a Log Analytics workspace and connect your SQL databases to it. Follow the instructions in Monitor Azure SQL Database with Azure Monitor metrics and alerts.

    enter image description here

    In the SQL database menu, select "Diagnostic settings". Click "Add diagnostic setting". Name the setting and select "Send to Log Analytics workspace". Choose your previously created Log Analytics workspace. Select the "SQLInsights" and "Errors" logs. You can choose any other tabs you want accordingly and save it

    enter image description here

    enter image description here

    Then, create your Log Analytics query that retrieves the DTU percentage for all databases. Something like this-

    AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL"
| where MetricName == "dtu_percent"
| summarize avg_value=avg(Average), count() by ResourceId

    Next, create an Azure Monitor alert that triggers when the average DTU percentage is greater than 95% for 10 minutes or more. Finally, configure the alert to run a SQL statement against the database that triggered the alert.

    enter image description here

    enter image description here

    Configure the alert to run a SQL statement against the database that triggered the alert. You can refer the steps from Create an alert rule on a metric from the Azure portal. In the Alert rule details, specify the SQL statement you want to run.

    You can find further details from these MS docs-