Monitoring On-Premise SQL Servers using Azure SQL Insights

In this article I would like to discuss about leveraging Azure SQL Insight to monitor your On-Premise SQL Workload. Microsoft announced Azure Monitor?SQL Insights?in public preview.?

SQL Insights is a remote, agent-based monitoring solution. The agent uses an open-source software called?Telegraf?and supports SQL Server running on a Virtual Machine (VM), Azure SQL Database, and Azure SQL Managed Instances. The agent runs on dedicated monitoring virtual machines to remotely collect data for SQL PaaS and SQL IaaS deployments. Furthermore, SQL Insights stores its data in Azure Monitor Logs (Workspace), allowing users to do aggregations, filtering, and data analysis.

The following diagram details the steps taken by information from the database engine and Azure resource logs, and how they can be surfaced.

Azure customers will not incur costs for using SQL Insights directly. They will get?charged?for its activity in the Log Analytics workspace (data ingested from agents and stored in the workspace) and any alerts and notifications configured on the log data. The service is currently in preview for SQL Databases from version 2012 and up, SQL Managed Instance, and SQL Server on Azure Virtual Machines.

By the end of this article you should be able to see various metrics for your on-premise SQL workload in the Azure Monitor, moreover, you can set alerts to receive notifications.

You will need below listed resources in Microsoft Azure to successfully configure the SQL Insights.

1. Azure Log Analytics Workspace
2. Azure Virtual Machine with Ubuntu 18.04 Image from Marketplace (custom images are currently not supported). Microsoft recommends allocating one virtual machine of size Standard_B2s for every 100 connection strings.
3. Key Vault (optional but recommended). It is used to store the SQL Connection String - yes, you read it correct - the solution uses SQL Connection string to connect to the DB and collect the Data and currently this is only supported method to gather the data.

Let's move on and see how the configuration is done to gather the data and view the different metrics.

Step - 1: Create a Log Analytics Workspace

Sign in to the Azure portal at?https://portal.azure.com.

In the Azure portal, click?All services. In the list of resources, type?Log Analytics. As you begin typing, the list filters based on your input. Select?Log Analytics workspaces.

Click?Add, and then provide values for the following options:

• Select a?Subscription?to link to by selecting from the drop-down list if the default selected is not appropriate.
• For?Resource Group, choose to use an existing resource group already setup or create a new one.
• Provide a name for the new?Log Analytics workspace, such as?DefaultLAWorkspace. This name must be unique per resource group.
• Select an available?Region. For more information, see which?regions Log Analytics is available in?and search for Azure Monitor from the?Search for a product?field.

Click?Review + create?to review the settings and then?Create?to create the workspace. This will select a default pricing tier of Pay-as-you-go which will not incur any charges until you start collecting a sufficient amount of data. For more information about other pricing tiers, see?Log Analytics Pricing Details.

Step - 2: Create a monitoring user on your on-premise SQL Server

Connect to your SQL Server Management Studio and execute below code to create a user for monitoring purpose with required privileges. Replace [user] with your own username and replace the 'mystrongpassword' with your own strong 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         

To verify execute below code in SQL Management Studio:

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

Step - 3: Create a Virtual Machine

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

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.

This step is straight forward, you type Virtual Machine in the Azure Portal Search bar and select Add Machine then select the Ubuntu Image 18.04 (I'm not sure whether Ubuntu 20.04 Image will work or not, let me know in the comments if you have tried it). Set the disk, network and other parameters.

After the VM is created, you need to allow on-premise SQL Server network access to the VM and this can be done by creating a Rule in NSG. The rule I have created is for testing only, in prod you should limit connections by specifying the destination ports.

Step - 4: Allow on-premise SQL server to accept connections from Azure VM

Azure SQL Insights collects the data from on-premise SQL server by connecting to it, so we need to publish the SQL Server on internet and allow traffic only from Azure VM's public IP. This can be achieved by configuring the router to NAT the public IP traffic to SQL Server or by using DynDNS like service (for testing). Also make sure the TCP/IP is enabled in the SQL Configuration. Restart the SQL Server for changes to reflect.

To verify that you can access the SQL Server using the Public IP, connect to the Database Engine from another computer outside your network.

Step - 4: Configure Azure Key Vault to securely store the connection string.

You need to supply a database connection string so that, Log Analytics VM can connect to the on-premise SQL Server, since this string is important and can be miss-used, you will need to store it in a secure place like Azure KeyVault.

Head over to the Key Vault from Azure portal search and create a Keyvault (if one doesn't exist), after the vault is created, make sure to add access policy for the VM created in the Step - 3 to read the secrets. Now, select the KeyVault name and choose Secrets from left pane and select Generate/Import. In the next screen, fill in the provision method as Manual and add a name for the string and add the connection string in the value field.

Step - 5: Finally its time to setup the SQL Insights

Open SQL Insights (preview) by selecting?SQL (preview)?from the?Insights?section of the?Azure Monitor?menu in the Azure portal. Click?Create new profile. 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.

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.

Next up is to add the Virtual Machine to the profile and this is done by selecting Add Monitoring Machine from Manage Profile tab. Specify the Virtual Machine name (created in Step - 3) and under the Connection String Code set the Secret Name and KeyVault URL (created in Step - 4).

????"version":?1,
????"secrets":?{
????????"telegrafPassword":?{
????????????"keyvault":?"https://mykeyvault.vault.azure.net/",
????????????"name":?"sqlPassword"
????????}
????},
????"parameters":?{
????????"sqlAzureConnections":?[],
????????"sqlVmConnections":?[],
????????"sqlManagedInstanceConnections":?[]
????}
}{        

Finally select Key Vault Subscription and Key Vault Name same as the one from Step - 4 and select Add Monitoring Machine to finish the wizard.

Wait for 10 mins or so for data to start populating and show in the dashboard. You can see status of VM is collecting under the status and if in case there is an there then you can click on link under Status (currently shows Collecting) to view the logs.

Once the data collection starts then we can see the charts getting displayed with different metrics like Transactions/sec, SQL Compilations/Sec, Batch Requests/sec, User Connections, etc. You can drill down the performance per Database and see the low level details. Optionally, you can export the data to excel and if you are looking for a specific metric not shown on the dashboard then you can look for it in Explore Metrics tab.

If you would like to set alerts then head over to the SQL Insights (preview) and select Alerts, you can define the alerts criteria by selecting the tab Alert Templates and setting the parameters. The rules which are active can be viewed in the tab Alert Rules.

All in all I believe this is a handy tool for DBAs and system admins to drill into the performance issues and identify the root cause in SQL environment. Azure SQL Insights allow you to start monitoring quickly and you can turn off the VM or delete the monitoring profile once you feel you no more require the need to monitor the SQL instances. I feel this approach makes life easier for admins and saves a lot of time and hassles, if a similar solution was to be deployed on-premise.

Syed Misbahuddin is a Certified Azure Solution Architect with over 14 year of experience in different industries. Feel free to get in touch with me if you need assistance with your cloud journey or solutions.

Visit this link to know more - Syed Misbhauddin Hussaini, PMP?, ITIL?, TOGAF?’s Services | LinkedIn