Enable SQL insights (preview)

This article describes how to enable SQL insights to monitor your SQL deployments. Monitoring is performed from an Azure virtual machine that makes a connection to your SQL deployments and uses Dynamic Management Views (DMVs) to gather monitoring data. You can control what datasets are collected and the frequency of collection using a monitoring profile.

Create Log Analytics workspace

SQL insights stores its data in one or more Log Analytics workspaces. Before you can enable SQL Insights, you need to either create a workspace or select an existing one. A single workspace can be used with multiple monitoring profiles, but the workspace and profiles must be located in the same Azure region. To enable and access the features in SQL insights, you must have the Log Analytics contributor role in the workspace.

Create monitoring user

You need a user on the SQL deployments that you want to monitor. Follow the procedures below for different types of SQL deployments.

Azure SQL database

Open Azure SQL Database with SQL Server Management Studio or Query Editor (preview) in the Azure portal.

Run the following script to create a user with the required permissions. Replace user with a username and mystrongpassword with a password.

CREATE USER [user] WITH PASSWORD = N'mystrongpassword'; 
GO 
GRANT VIEW DATABASE STATE TO [user]; 
GO 

Create telegraf user script.

Verify the user was created.

Verify telegraf user script.

Azure SQL Managed Instance

Log into your Azure SQL Managed Instance and use SQL Server Management Studio or similar tool to run the following script to create the monitoring user with the permissions needed. Replace user with a username and mystrongpassword with a password.

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

SQL Server

Log into your Azure virtual machine running SQL Server and use SQL Server Management Studio or similar tool to run the following script to create the monitoring user with the permissions needed. Replace user with a username and mystrongpassword with a password.

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

Create Azure Virtual Machine

You will need to create one or more Azure virtual machines that will be used to collect data to monitor SQL.

Note

The monitoring profiles specifies what data you will collect from the different types of SQL you want to monitor. Each monitoring virtual machine can have only one monitoring profile associated with it. If you have a need for multiple monitoring profiles, then you need to create a virtual machine for each.

Azure virtual machine requirements

The Azure virtual machines has the following requirements.

Note

The Standard_B2s (2 cpus, 4 GiB memory) virtual machine size will support up to 100 connection strings. You shouldn't allocate more than 100 connections to a single virtual machine.

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.

Configure network settings

Each type of SQL offers methods for your monitoring virtual machine to securely access SQL. The sections below cover the options based upon the type of SQL.

Azure SQL Databases

SQL insights supports accessing your Azure SQL Database via it's public endpoint as well as from it's virtual network.

For access via the public endpoint, you would add a rule under the Firewall settings page and the IP firewall settings section. For specifying access from a virtual network, you can set virtual network firewall rules and set the service tags required by the Azure Monitor agent. This article describes the differences between these two types of firewall rules.

Set server firewall

Firewall settings.

Azure SQL Managed Instances

If your monitoring virtual machine will be in the same VNet as your SQL MI resources, then see Connect inside the same VNet. If your monitoring virtual machine will be in the different VNet than your SQL MI resources, then see Connect inside a different VNet.

Azure virtual machine and Azure SQL virtual machine

If your monitoring virtual machine is in the same VNet as your SQL virtual machine resources, then see Connect to SQL Server within a virtual network. If your monitoring virtual machine will be in the different VNet than your SQL virtual machine resources, then see Connect to SQL Server over the internet.

Store monitoring password in Key Vault

You should store your SQL user connection passwords in a Key Vault rather than entering them directly into your monitoring profile connection strings.

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 permissions such as User Access Administrator or Owner

A new access policy will be automatically created as part of creating your SQL Monitoring profile that uses the Key Vault you specified. Use Allow access from All networks for Key Vault Networking settings.

Create SQL monitoring profile

Open SQL insights by selecting SQL (preview) from the Insights section of the Azure Monitor menu in the Azure portal. Click Create new profile.

Create new profile.

The profile will store the information that you want to collect from your SQL systems. It has specific settings for:

  • Azure SQL Database
  • Azure SQL Managed Instances
  • SQL Server running on virtual machines

For example, you might create one profile named SQL Production and another named SQL Staging with different settings for frequency of data collection, what data to collect, and which workspace to send the data to.

The profile is stored as a data collection rule resource in the subscription and resource group you select. Each profile needs the following:

  • Name. Cannot be edited once created.
  • Location. This is an Azure region.
  • Log Analytics workspace to store the monitoring data.
  • Collection settings for the frequency and type of sql monitoring data to collect.

Note

The location of the profile should be in the same location as the Log Analytics workspace you plan to send the monitoring data to.

Profile details.

Click Create monitoring profile once you've entered the details for your monitoring profile. It can take up to a minute for the profile to be deployed. If you don't see the new profile listed in Monitoring profile combo box, click the refresh button and it should appear once the deployment is completed. Once you've selected the new profile, select the Manage profile tab to add a monitoring machine that will be associated with the profile.

Add monitoring machine

Select Add monitoring machine to open a context panel to choose the virtual machine to setup to monitor your SQL instances and provide the connection strings.

Select the subscription and name of your monitoring virtual machine. If you're using Key Vault to store your password for the monitoring user, select the Key Vault resources with these secrets and enter the URL and secret name to be used in the connection strings. See the next section for details on identifying the connection string for different SQL deployments.

Add monitoring machine.

Add connection strings

The connection string specifies the username that SQL insights should use when logging into SQL to run the Dynamic Management Views. If you're using a Key Vault to store the password for your monitoring user, provide the URL and name of the secret to use.

The connections string will vary for each type of SQL resource:

Azure SQL Databases

Enter the connection string in the form:

sqlAzureConnections": [ 
   "Server=mysqlserver.database.windows.net;Port=1433;Database=mydatabase;User Id=$username;Password=$password;" 
}

Get the details from the Connection strings menu item for the database.

SQL database connection string

To monitor a readable secondary, include the key-value ApplicationIntent=ReadOnly in the connection string.

Azure virtual machines running SQL Server

Enter the connection string in the form:

"sqlVmConnections": [ 
   "Server=MyServerIPAddress;Port=1433;User Id=$username;Password=$password;" 
] 

If your monitoring virtual machine is in the same VNET, use the private IP address of the Server. Otherwise, use the public IP address. If you're using Azure SQL virtual machine, you can see which port to use here on the Security page for the resource.

SQL virtual machine security

To monitor a readable secondary, include the key-value ApplicationIntent=ReadOnly in the connection string.

Azure SQL Managed Instances

Enter the connection string in the form:

"sqlManagedInstanceConnections": [ 
      "Server= mysqlserver.database.windows.net;Port=1433;User Id=$username;Password=$password;", 
    ] 

Get the details from the Connection strings menu item for the managed instance.

SQL Managed Instance connection string

To monitor a readable secondary, include the key-value ApplicationIntent=ReadOnly in the connection string.

Monitoring profile created

Select Add monitoring virtual machine to configure the virtual machine to collect data from your SQL resources. Do not return to the Overview tab. In a few minutes, the Status column should change to read "Collecting", you should see data for the SQL resources you have chosen to monitor.

If you do not see data, see Troubleshooting SQL insights to identify the issue.

Profile created

Note

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.

Next steps