How to Enable SQL Insights (preview) to monitor your SQL deployments

Synopsis of this post:

1. What is SQL Insights
2. Take note of the current limitations of SQL Insights (preview)
3. Pricing
4. Regional Availability
5. Components required for SQL Insights provisioning
   • SQL backend
   • Log Analtyics Workspace
   • Ubuntu virtual machine
   • Azure Key Vault
   • Azure SQL telegraf user*
   • SQL Monitor profile
6. Provisioning of SQL Insights
   • Step 1 – Provision SQL database
     • System Assigned Managed Identity
   • Step 2 – Configure Azure SQL Database Firewall settings
   • Step 3 – Provision a destination Log Analytics workspace
   • Step 4 – Provision the Data Collector Ubuntu Azure Virtual Machine
     • The Azure Ubuntu virtual machine requirements
     • System Assigned Managed Identity
     • Enable Service Endpoint “Microsoft.Sql”
   • Step 5 – Provision an Azure Key Vault
     • Access Policy
     • Generate Secret
   • Step 6 – Create the SQL monitoring telegraf user account
   • Step 7.1 – Create a SQL monitoring profile
   • Step 7.2 – Adding a monitoring machine
   • Step 8 – Verify Collection logs

My post presumes that you already have a backend Azure SQL database in place and that you now want to enable SQL Insights. I have written this post using Azure SQL database but Azure SQL Insights can be used on Azure SQL Managed Instance and SQL Server ( Azure SQL family).

My post describes how to enable SQL Insights (preview) to monitor your SQL deployments. SQL Monitoring is performed via an Azure Ubuntu virtual machine that makes a connection to your SQL deployments to gather monitoring data. SQL Monitoring Profiles create Data Collection Rules (DCRs) that control what datasets are collected and the frequency of collection on each dedicated vm.

1. What is SQL Insights?

SQL Insights (preview) is a comprehensive solution for monitoring any product in the Azure SQL family. SQL Insights uses dynamic management views to expose the data that you need to monitor SQL health, diagnose problems, and tune performance.

SQL Insights performs all monitoring remotely. Monitoring agents are installed on dedicated (Ubuntu) virtual machines which connect to your SQL resources via DCR deployment and remotely gather data. The gathered data is stored in destination Log Analytics Workspace/s for easy aggregation, filtering, and trend analysis

The diagram below details the flow of information from the database engine and Azure resource logs, and how they can be parsed.

2. Current Limitations of SQL Insights (preview):

• Non-Azure instances: SQL Server running on virtual machines outside Azure is not supported.
• Azure SQL Database elastic pools: Metrics can’t be gathered for elastic pools or for databases within elastic pools.
• Azure SQL Database low service tiers: Metrics can’t be gathered for databases on Basic, S0, S1, and S2 service tiers.
• Azure SQL Database serverless tier: Metrics can be gathered for databases through the serverless compute tier. However, the process of gathering metrics will reset the auto-pause delay timer, preventing the database from entering an auto-paused state.
• Secondary replicas: Metrics can be gathered for only a single secondary replica per database. If a database has more than one secondary replica, only one can be monitored.
• Authentication with Azure Active Directory: The only supported method of authentication for monitoring is SQL authentication. For SQL Server on Azure Virtual Machines, authentication through Active Directory on a custom domain controller is not supported.

3. Pricing

There is no cost for the SQL Insights (preview) service. All costs are incurred by the virtual machines that gather the data, and the Log Analytics workspaces that store the ingested data, as well as any alert rules configured on the data.

4. Regional availability

SQL Insights (preview) is available in all Azure regions where Azure Monitor is available, with the exception of Azure Government and national clouds.

5. Components required for SQL Insights provisioning

• SQL backend
• Log Analtyics Workspace
• Ubuntu virtual machine
• Azure Key Vault
• Azure SQL telegraf user*
• SQL Monitor profile

6 -Provisioning of SQL Insights

Step 1 – Provision SQL database

Once deployed > go to your SQL database > Query editor (preview) > login with telegraf > and run the following script to make sure your table is working:

SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName
FROM SalesLT.ProductCategory pc
JOIN SalesLT.Product p
ON pc.productcategoryid = p.productcategoryid;

System Assigned Managed Identity

Once the SQL server has been deployed, go to the Identity tab and enable the System Assigned Managed Identity for this SQL Server. (This MI will be used later in the Azure Key Vault Access Policy).

Step 2 – Configure Azure SQL Database Firewall settings

Firewall Rules

SQL Insights supports accessing your Azure SQL Database via its public endpoint as well as from its 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.

On my Azure SQL Server I have added my client IP to the SQL server firewall rules under the Public Access Tab.

Connectivity

Microsoft recommends changing the connectivity policy to Redirect for direct connectivity to the node hosting the database. (This is optional and is what I tested).

Step 3 – Provision a destination Log Analytics Workspace

SQL Insights stores its data in a destination Log Analytics workspaces. A single workspace can be used an an endpoint for multiple monitoring profiles, but the workspace and profiles and the ubuntu server must be located in the same Azure region. To enable and access the features in SQL Insights, ensure that you have the Log Analytics contributor role.

Step 4 – Provision the Data Collector Ubuntu Azure Virtual Machine

You will need to deploy one or more Azure virtual machines onto which the agents will be installed that will be used to collect data to monitor SQL.

A SQL Monitoring profile provisions a DCR under-the-hood that you will use to configure and collect data from the different types of SQL sources that you want to monitor and as such will be linked to a DCR Resource (vm) which will be an Ubuntu virtual machine.

Each SQL Monitoring Ubuntu virtual machine can only have one dedicated monitoring profile associated with it. If you have a need to provision multiple SQL monitoring profiles, then you will need to deploy a virtual machine for each.

System Assigned Managed Identity

Once the Ubuntu server has been deployed, go to the Identity tab and enable the System Assigned Managed Identity for this virtual machine. (This MI will be used later in the Azure Key Vault Access Policy).

Enable Service Endpoint “Microsoft.Sql”

Go to the Ubuntu vm subnet and enable the Service Endpoint “Microsoft.Sql” via the Portal or via Powershell.

Register-AzResourceProvider '
-ProviderNamespace "Microsoft.Sql"

Network Security Group

Create and add a new NSG to the Ubuntu vm subnet, with a service tag which represents the SQL Azure Service. Add the tag SQL and SqlManagement

https://learn.microsoft.com/en-us/azure/virtual-network/service-tags-overview

Step 5 – Provision an Azure Key Vault

As a security best practice, Microsoft strongly recommends that you store your SQL user (login) passwords in a Key Vault, rather than entering them directly into your monitoring profile connection strings.

#register the destination subscription if not registered
Register-AzResourceProvider -ProviderNamespace "Microsoft.KeyVault"

#create the new standard Key Vault with basic configurations
$kvName = "allensKeyVault01"
$rgname = "rg-allen"
$location = "southafricanorth"
$sku = "standard"
New-AzKeyVault -Name $kvName -ResourceGroupName $rgname -Location $location -sku $sku

Now that the Key Vault is configured, we need to get the Azure SQL server System Assigned MI and the Ubuntu server System Assigned MI added to the Key Vault Access Policy.

Access Policy

On the Key Vault Access Policy the following configs are required:

Generate Secret

A secret needs to be generated which will contain the password for the admin account = telegraf

This secret will be referenced via the connection strings each time Insights queries the Ubuntu vm to pull SQL data.

Step 6 – Create the SQL monitoring telegraf user account

You need a SQL user (login) account on the SQL instance that you want to monitor.
The steps below cover the process per Azure SQL Database.

You may connect to an Azure SQL database with SQL Server Management Studio, Query Editor (preview) in the Azure portal, or Azure Data Studio.

I ran the following script to create a new user with the required permissions from Query Editor. Replace the value ‘user’ with a username and mystrongpassword with a strong password.

(I decided to use a super strong password for simplicity during this deployment and used the telegraf account as the instance admin account since Im using a sandbox database. You will use your bespoke admin account in place of this and grant the account VIEW DATABASE permissions. This is the only part you will need to figure out on your own as per your bespoke enviroment.)

CREATE USER [telegraf] WITH PASSWORD = N'P@ssw0rd';
GO
GRANT VIEW DATABASE STATE TO [telegraf];
GO

Verify the success of the new user creation in the Messages window: Query succeeded

I then decided to test using the Azure Data Studio, where I verify that the new user was in fact created.

I used the following script: (you dont need to edit this script, run as is)

select name as username,
       create_date,
       modify_date,
       type_desc as type,
       authentication_type_desc as authentication_type
from sys.database_principals
where type not in ('A', 'G', 'R', 'X')
       and sid is not null
order by username

Step 7.1 – Create a SQL monitoring profile

Open SQL Insights (preview) by selecting Azure portal > Azure Monitor > Insights > SQL (preview). Select Create new profile.

The profile will store the information that you want to collect from either of your SQL systems by creating a DCR. Your SQL Profile name will become the DCR name.

Collection settings are for the frequency and type of sql monitoring data to collect. This is where the “Data Sources” component of the DCR is provisioned.

Once you have added the destination Log Analytics Workspace then select Create Monitoring Profile > Create SQL monitoring profile

Step 7.2 – Adding a monitoring machine

Upon deployment of the SQL monitoring profile you will be met with an error message “This profile does not have any associated monitoring virtual machines..”

Select Manage Profile tab > Add monitoring machine to open a context panel to choose the virtual machine from which to monitor your SQL instances and provide the connection strings.

Editing the Connection String:

Inside the Connection string block, edit and insert your bespoke values according to:

1. Name of the secret that you created in your key vault.
2. The full URI of your key vault where the secret is located.
3. Update your SQL server and SQL database values.

Thats all, then select the subscription and key vault in the drop down list below and Update Monitoring Configuration

This section will populate the the “Data Sources” component of the DCR.

Verification Checks

You can run the following SQL queries to check the validity of your account (I could not get these working on any tooling). I wasnt too worried because remember that I had created the SQL server with telegraf as the SQL Server Admin account. If this server is in production, then get your SQL guys to make the magic happen.

This verification is to check your SQL Firewalls and NSG’s have been created. We have already created all the required components above.

Step 8 – Verify Collection logs

Overview Tab

Upon completion, go to SQL Monitor > Overview Tab and verify the status of your connected database.

If you click on the database link you will find the high level view of all your connected databases.

If you click on the target database link allendb01 you will find a deep dive view of more information.

Manage Profile Tab

Upon completion, go to SQL Monitor > Manage Profile Tab and verify whether the status has changed to Collecting

Then click on Collecting to deep dive the logs.

You can select All Logs or Errors and then scope down by time.

Microsoft Reference:
https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-insights-enable?view=azuresq

The intention of this post is to simplify the deployment of SQL Insights after repeated deployments.

I hope the steps above simplify your deployment.