Azure SQL Analytics solution – Public Preview

  • Article

Microsoft Azure SQL Database is a scalable relational database service that provides capabilities that are familiar to SQL Server to applications that run in Azure. Azure SQL Analytics, which part of OMS Insight and Analytics, collects and visualizes the important SQL Azure performance metrics that enable users to easily create custom monitoring rules and alert on these rules under defined scenarios. The solution, now in public preview, enables you to monitor across multiple Azure subscriptions, resources and elastic pools. More importantly, you can identify issues at each layer of your application stack.

Screenshot showing monitoring capabilities

By using the Azure SQL Analytics solution, you can capture metrics (across subscriptions and elastic pools) from Azure SQL Database and elastic pools and visualize them in Operations Management Suite (Log Analytics). This solution takes advantage of Azure Diagnostic metrics and Log Analytics views to present data about all your instances of Azure SQL Database and elastic pools in a single log analytics workspace.

Prerequisites

How do I get started?

  1. In the Azure portal, click the Marketplace tile, click Monitoring + Management, and search for Azure SQL Analytics, and then click Azure SQL Analytics in the search results.
  2. Click the Create button to start the configuration wizard in the Azure portal and configure the solution.  Screenshot of Azure SQL Analytics (Preview), with Create button
  3. Follow the steps in the UI to start the installation and configuration of this solution.

 

Support for more than one Azure subscription (Advanced scenario)

To support multiple subscriptions, use the PowerShell script in the Enable Azure resource metrics logging using PowerShell blog post. Simply provide the workspace resource ID as a parameter when you execute the script to send diagnostic data from resources in one Azure subscription to an OMS workspace in another Azure subscription.

Example

PS C:\> $WSID = "/subscriptions/<subID>/resourcegroups/oms/providers/microsoft.operationalinsights/workspaces/omsws"

PS C:\> .\Enable-AzureRMDiagnostics.ps1 -WSID $WSID

Analyze data and create alerts

Using the data coming from Azure SQL Database resources you can easily create alerts. Here are few useful queries that you can use for alerting:

High DTU on Azure SQL Database

Type=AzureMetrics ResourceProvider="MICROSOFT.SQL" ResourceId=*"/DATABASES/"* MetricName=dtu_consumption_percent | measure Avg(Average) by Resource interval 5minutes

High DTU on Azure SQL Database Elastic Pool

Type=AzureMetrics ResourceProvider="MICROSOFT.SQL" ResourceId=*"/ELASTICPOOLS/"* MetricName=dtu_consumption_percent | measure avg(Average) by Resource interval 5minutes

These alert-based queries can be used to alert on specific thresholds for both Azure SQL Database and elastic pools. To configure an alert for your OMS workspace:

  1. Go to https://mms.microsoft.com.
  2. Authenticate to the OMS workspace that you have configured for this solution.
  3. Click on Log Search
  4. Select alert from the list of options                                                                                                                             Screenshot of Log Search, with Alert option highlighted
  5. Configure the appropriate properties and the specific thresholds                                                                       Screenshot of Log Search Add Alert Rule

 

One of the most useful queries that you can perform is to compare the DTU utilization across all Azure SQL Elastic Pools across all your subscriptions. Database Throughput Unit (DTU) provides a way to describe the relative capacity of a performance level of Basic, Standard, and Premium databases and pools. DTUs are based on a blended measure of CPU, memory, reads, and writes. As DTUs increase, the power offered by the performance level increases. For example, a performance level with 5 DTUs has five times more power than a performance level with 1 DTU. A maximum DTU quota applies to each server and elastic pool.

By running the following query, you can easily tell if you are underutilizing or over utilizing your SQL Azure elastic pools.

Type=AzureMetrics ResourceId=*"/ELASTICPOOLS/"* MetricName=dtu_consumption_percent | measure avg(Average) by Resource | display LineChart

In the following example, we can clearly see one elastic pool has a heavy spike near 100% DTU.  We can then use this information to troubleshoot potential recent changes in our environment by using Azure Activity logs.

Line chart of elastic pool DTU metrics

Thank you!

We hope you find this solution useful to help you to gain more insights into your Azure SQL environments. Your feedback helps drive innovation in our solutions.

Jim Britt

Senior Program Manager