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

Question

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

Answer 1

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.

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

Answer 2

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.

Answer 3

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