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

sushibee 0 Reputation points
2024-11-20T07:03:50.9633333+00:00

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.

Azure Monitor
Azure Monitor
An Azure service that is used to collect, analyze, and act on telemetry data from Azure and on-premises environments.
3,365 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Ashok Gandhi Kotnana 1,185 Reputation points Microsoft Vendor
    2024-11-20T11:38:36.6366667+00:00

    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.    

    User's image

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


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.