Azure SQL Analytics solution now in Public Preview. Azure SQL 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, enables you to monitor across multiple Azure subscriptions, resources and elastic pools.

By using the Azure SQL Analytics solution, you can capture metrics from Azure SQL Database and elastic pools and visualize them in Operations Management Suite. 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.

How to start

  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.

3. Follow the steps in the UI to start the installation and configuration of this solution.

Advanced scenario with multiple subscriptions

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

Analyze data and create alerts

The solution ships with a handful of useful queries to get started analyzing data that you find when you go to the solution view and scrolling to the far right.

We’ve provided a few alert-based queries in the list that you can use to alert on specific thresholds for both Azure SQL Database and elastic pools. To configure an alert for your OMS workspace:

  1. Go to http://mms.microsoft.com.
  2. Authenticate to the OMS workspace that you have configured for this solution.
  3. Open the solution view for Azure SQL Analytics in your OMS workspace.
  4. Scroll to the far right, and select the query on which you want to create an alert.

5. Select alert from the list of options.

6. Configure the appropriate properties and the specific thresholds.

Example

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.

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

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.