On-premise MSSQL DB monitoring using Azure Monitor and Log Analytics

Question

Is it possible to set up a monitoring dashboard in azure to track the performance of on-premise MSSQL databases, including metrics like blocking queries, resource usage, and the top 10 queries consuming CPU and memory, etc.?

Can this be done using only Azure Monitor and Log Analytics?

The on-premise servers already have the Azure Arc agent installed, which is used for monitoring security things through Sentinel. I'm unsure if an additional agent is required to configure Azure Monitor or Log Analytics.

Kindly advise if anyone has done this before, and please share the steps.

Answer 1

Hi sushibee,
Welcome to Microsoft Q&A Forum, thank you for posting your query here!

Yes, you can set up a monitoring dashboard in Azure to track the performance of your on-premises MSSQL databases using Azure Monitor and Log Analytics. Since the on-premises servers already have the Azure Arc agent installed, you are partially set up. However, you’ll likely need to add SQL Insights for deeper database-level monitoring.

Please have a look into the below suggestions

1. Azure Monitor

Azure Monitor provides performance monitoring for databases.

Key Metrics:

1. CPU, memory, and disk usage.
2. Blocking and deadlock counts.
3. SQL Server-specific performance counters (e.g., Batch Requests/sec, Wait Statistics). How to Enable:

Use Diagnostic Settings on the Azure Arc-enabled SQL Server to forward metrics and logs to Azure Monitor.

 

2. SQL Insights (via Azure Monitor)

SQL Insights is a detailed monitoring solution specifically designed for SQL Servers.

Capabilities:

Blocking Queries: Identify queries causing blocks.

Top 10 Queries: Identify resource-intensive queries by CPU and memory.

Wait Statistics: Analyse wait events that impact query performance. Query execution time metrics

Refer: https://medium.com/life-at-apollo-division/how-to-use-azure-sql-insights-to-monitor-your-on-premise-sql-workload-40db77c08a96

3.Use Azure Log Analytics for Centralized Logging

Azure Log Analytics

Azure Log Analytics allows you to analyze SQL Server logs and metrics for detailed insights.

• Uses:
  • Query logs for blocking query details.
  • Generate custom reports for top queries by resource usage.
  • Correlate database logs with server-level performance data. Please let us know if you have any further queries. I’m happy to assist you further.    

---

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members.