SQL PaaS DB monitor

[Rahul_Mahajan]

Hi All,

How to monitor all database in Azure SQL using log analytics, instead of creating different alerts for each database.

Thanks in advance.

[#2]

@Rahul_Mahajan 

Have you looked at https://docs.microsoft.com/en-us/azure/azure-monitor/platform/alerts-action-rules ?

[#3 · Accepted solution]

Hi@Rahul_Mahajan 

the first thing that you want to do is to enable diagnostic logs on every Azure SQL logical server and database:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-metrics-diag-logging

After that based on those logs and metrics you can create Log Analytics alerts. No matter if you are creating log analytics alert based on metrics data or diagnostic data (events) you would want to aggregate upon _ResourceId. That is unique for every resources so it will allow you to fire up alerts er instance. I cannot give the query for every alert that you can create as that will depend on what you want to monitor but I will give you one example to get you started:

AzureMetrics | where ResourceProvider =~ 'microsoft.sql' and MetricName =~ 'cpu_percent' | summarize AggregatedValue = avg(Maximum) by ResourceId, bin(TimeGenerated, 5m)

The below query can be used in alert to monitor the threshold. It should metric measurement alert and the threshold is set on the alert rule depending on your preference.

Let me know if this is sufficient for you to get started.

[#4]

@Stanislav Zhelyazkov 

Yes, will play with it and get back if anything more needed. Thanks