Apr 8, 2022

6 min read

Enabling SQL insights with Azure Monitor

How to setup Azure Monitor for SQL insights (Public Preview)

Overview

Monitoring is the very first step to optimize workload performance. It gives you a bird-eye view of the current and previous estate. Analyzing monitoring data can provide deep insights to tune the right knob for the performance. It also provides the root cause analysis for most of the challenging performance issues.

SQL Server monitoring is about continuous collection of data related to usage, performance, event metrics, and so on. SQL Servers are very critical workload for organizations. Ensuring its availability, health, and performance is an ultimate responsibility within the enterprises.

In the article, I’ll be describing how to enable SQL Insights to monitor SQL workload. Technically, monitoring is performed inside an Azure virtual machine which makes a connection to the SQL server and uses Dynamic Management Views (DMVs) to collect monitoring data. Furthermore, it gives you the ability to control what datasets are collected and frequency of collection by using the monitoring profile.

In this article, I’ll be setting up Azure SQL Managed Instance for monitoring. However, it can be utilized for Azure SQL Database and SQL Server running in VM as well. Monitoring VM will be used to collect data for SQL instance.

Below are the steps to setup Azure monitor for SQL insights.

Provision Log Analytics workspace

Provision log analytics workspace before you enable SQL insights in Azure monitor. you can also utilize an existing one if it is already created. SQL insights stores its data in one or more log analytics workspace, and single workspace can be used with multiple monitoring profile. Make sure, the user enabling the SQL insight should have log analytics contributor role in the workspace.

Provision Virtual Machine (VM)

Create a VM. This VM will act as a plugin-driven server agent for reporting metrics into Azure Monitor for SQL insights.

The Azure virtual machine has the following requirements:

Operating system: Ubuntu 18.04 using Azure Marketplace image. Custom images are not supported.
Recommended minimum Azure virtual machine sizes: Standard_B2s (2 CPUs, 4 GiB memory)
Deployed in any Azure region supported by the Azure Monitor agent, and meeting all Azure Monitor agent prerequisites.
Depending upon the network settings of your SQL resources, the virtual machines may need to be placed in the same virtual network as your SQL resources so they can make network connections to collect monitoring data.

Under networking section, add firewall outbound rule to allow port 1433

Create a monitoring user in SQL Server

You need a user (login) on the SQL instances that you would like to monitor. This user credentials will later be stored in Azure Key Vault (AKV). This user will be used by the monitoring VM for establishing a connection to the SQL server being monitor.

Connect to your Azure SQL Managed Instance either using SQL Server Management Studio (SSMS) or Azure Data Studio and execute the following SQL script as an example.

USE master;
GO
CREATE LOGIN [telegraf] WITH PASSWORD = N'strongpassword';GO
GRANT VIEW SERVER STATE TO [telegraf];GO
GRANT VIEW ANY DEFINITION TO [telegraf];GO

Store monitoring user’s password in the Azure Key Vault

It is highly recommended as a security best practice to store your SQL login user’s password in Azure Key Vault instead of hard coding it inside the monitoring profile connection string.

When settings up your profile for SQL monitoring, you will need one of the following permissions on the Key Vault resource you intend to use:

Microsoft.Authorization/roleAssignments/write
Microsoft.Authorization/roleAssignments/delete

If you have these permissions, a new Key Vault access policy will be automatically created as part of creating your SQL Monitoring profile that uses the Key Vault you specified.

You also need to make sure the monitoring VM should have access to the Key Vault.

Create SQL Monitoring Profile

Now visit Azure Monitor and Open SQL insights by clicking SQL (preview) from the Insights section as shown in the below figure. Click “Create new profile”

Fill up the “Create new profile” form

Click “Create monitoring profile” once you’ve entered the details for your monitoring profile.

After its creation, select the “Manage profile” tab to add a monitoring machine that will be associated with the profile.

Add monitoring machine

Clicking “Add monitoring machine” will launch the following panel.

Make sure to change the monitoring configuration according to your environment. you need the following.

Key Vault URI
Secret name
SQL server host name
monitoring user userid created in the earlier steps.

After completing the panel information. click “Add monitoring virtual machine” button.

Watch the monitoring state and wait until the status changes to “Collecting”

If there’s any issues arise, status will report to “Not Collecting” as shown below.

Click “Not Collecting” to dig deeper. Then click on Error to see what is causing the failure to capture SQL metrics. In some case, it could be login failure to SQL instance or incorrect database, and so on. Resolve those issues to establish successful collection status.

Once all the issues are resolved or if there are no issues, status of monitoring machine will change to “collecting” as shown below.

After successful setup of monitoring machine, monitoring profile will be created. Click on “Overview” tab. As you can see in the below figure, it shows Azure SQL Managed Instance and 8 databases. click on “instance” to view more details.

If you need to update your monitoring profile or the connection strings on your monitoring VMs, you may do so via the SQL insights Manage profile tab. Once your updates have been saved the changes will be applied in approximately 5 minutes.