Enabling SQL insights with Azure Monitor

Overview

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

Provision Log Analytics workspace

Log Analytics Workspace

Provision Virtual Machine (VM)

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

Create a monitoring user in SQL Server

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

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

Create SQL Monitoring Profile

Add monitoring machine

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

Review Monitoring Data

Overview

Activities

Database space

Transaction

Database IO

Explore Metrics

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Afzal Muhammad

Afzal Muhammad

Innovative and transformative cross domain cloud solution architect @Microsoft (& xCisco). Helping companies to digitally transform!!!