How to monitor SQLServer process from an Azure Virtual Machine

Question

Hi, I would like to create an alert to monitor the process of my SQLServer that is located in an Azure Virtual Machine.

Also I would like to monitor other process but I think the method will be similar.

Answer 1

Hi @Juan Manuel Mariscal Millán

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.
2. 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:

          // 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
     

3. 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.

See Azure VMs with Azure Monitor to find more information about monitoring an Azure Virtual Machine.