How to monitor SQLServer in a windows Azure Virtual Machine and send logs to log analystic workspace

Harsha Parasa 20 Reputation points
2024-05-31T07:57:25.2566667+00:00

How to monitor SQLServer in a windows Azure Virtual Machine and send logs to log analystic workspace

SQL Server on Azure Virtual Machines
Azure Monitor
Azure Monitor
An Azure service that is used to collect, analyze, and act on telemetry data from Azure and on-premises environments.
2,925 questions
Azure Virtual Machines
Azure Virtual Machines
An Azure service that is used to provision Windows and Linux virtual machines.
7,385 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Sohail Ali 315 Reputation points Microsoft Employee
    2024-05-31T08:50:36.98+00:00

    You can refer to article https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-insights-overview?view=azuresql

    Or

    You can enable the diagnostic settings for SQL database as stand alone and get below information.

    User's image

    However if you want to do anything custom, you might have to write some automation to read SQL tables and make them as https://learn.microsoft.com/en-us/azure/azure-monitor/agents/data-sources-custom-logs so that agent can pick it up or send it to Log analytics workspace as customlogs using the data collector api.

    Please be aware that you will be only able to query Log Analytics workspace using Kusto queries not T-SQL Query.

    Note: If your SQL tables are huge or you are running production workload , the custom approaches might not be recommended.

    Hope the above information helps. Please let us know if you need any further information. Thank you

    0 comments No comments

  2. Harsha Parasa 20 Reputation points
    2024-05-31T09:25:35.5433333+00:00

    You can use the following as a guide to create specific monitoring but...

    1. Enable diagnostics settings for the VM: To collect performance data from the VM, you need to enable diagnostics settings for the VM. You can do this by following these steps:
      • Go to the Azure portal and navigate to the VM that has SQL Server installed.
        • Click on the "Diagnostics settings" option under the Monitoring section.
          • Click on the "Add diagnostic setting" button.
            • In the "Diagnostic settings" blade, select the "Performance counters" option.
              • Select the performance counters you want to monitor for SQL Server. For example, you can select the "Processor" and "Memory" counters.
                • Choose the destination for the performance data. You can choose to send the data to a storage account or to Log Analytics.
                  • Click on the "Save" button to save the diagnostic settings.
                  1. Create a log search query: Once you have enabled diagnostics settings for the VM, you can create a log search query to monitor the performance data. To create a log search query, follow these steps:
                    • Go to the Azure portal and navigate to the Log Analytics workspace where you want to create the query.
                      • Click on the "Logs" option under the General section.
                        • In the query editor, enter the following query to retrieve the performance data for SQL Server:KustoCopy
                                                           // This query retrieves the average page life expectancy for SQL Server every 5 minutes.
                        
        Perf | where ObjectName == "SQLServer:Buffer Manager" | where CounterName == "Page life expectancy" | summarize avg(CounterValue) by bin(TimeGenerated, 5m), Computer ```
    2. Create an alert rule: Once you have created the log search query, you can create an alert rule to notify you when the performance data meets certain conditions. To create an alert rule, follow these steps:
    • In the query editor, click on the "New alert rule" button.
    • In the "New alert rule" blade, configure the alert rule settings. For example, you can set the alert rule to trigger when the page life expectancy drops below a certain threshold.
    • Choose the action to take when the alert rule triggers. For example, you can send an email notification or trigger an Azure Function.
      • Click on the "Create alert rule" button to create the alert rule.
    0 comments No comments

  3. ShaktiSingh-MSFT 14,186 Reputation points Microsoft Employee
    2024-06-03T05:54:39.44+00:00

    Hi Harsha Parasa •,

    Welcome to Microsoft Q&A forum.

    As I understand, you want to monitor SQL Server in Azure VM via Azure Log Analytics.

    You can configure Azure Monitor SQL Insights for SQL-specific metrics for Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs. For more information about Azure Monitor SQL Insights for all products in the Azure SQL family, see Monitor your SQL deployments with SQL Insights (preview).

    Also refer to similar Q&A post which might help:

    https://learn.microsoft.com/en-us/answers/questions/520377/log-analytics-query-for-sql-on-azure-vm

    Let us know what problem you are facing as per your comment in the Azure Portal when enabling Diagnostic settings.

    Thanks

    0 comments No comments