Prashanth Jayaram
Diagnostic settings - gather metrics

Getting started with Azure SQL Analytics

July 29, 2020 by

Using Azure SQL Analytics, you will rediscover the new ways to manage the Azure SQL Database. Microsoft is driving an intelligent platform to provide seamless collaboration for all their end users to business analysts, by building an effective Log Analytics stack and intelligent insight through dashboards.

Introduction

The emerging force in the industry is by far Cloud technology. Did you ever get a chance to work on Azure SQL Analytics? I feel it is worth giving a try. Let us get started. Azure SQL database—is commonly known as Azure SQL is a relational database service. It provides relational SQL Server like capabilities to the applications that are running in Azure cloud. It is highly scalable and available to the applications. Azure SQL database meets the SLA 99.995% availability for the zone redundant databases. However, Microsoft strategically brought Analytics integration to Azure SQL database. I see it is a real game-changer.

In this article, you will see how to monitor the database metrics using Azure analytics solution.

  1. Analytics in general
  2. Understand Operational Management Suite(OMS)
  3. Configure Log Analytics workspace using Azure Market Place
  4. Create an Azure SQL Analytics
  5. Integrate Azure SQL Analytics with Log Analytics workspace
  6. Configure data stream of telemetry data into the Log Analytics workspace
  7. Define Kusto Query
  8. And more…

Why do we need Analytics?

Analytics is a methodology where you’ll analyze the statistical data to gain insight into the more meaningful details about the data.

Analytics is a process of turning the raw data into insightful data that will lead to building effective decision-making systems. Analytics heavily relies on the type of data gathering systems. The humans are using a more sophisticated system that deals with billions of data points—Analytics is very useful in the areas where we gather a huge amount of data to derive more meaningful insights for the data to make storytelling much easier.

What is Azure SQL Analytics?

Azure SQL Analytics (Preview function) is a cloud-based solution that gathers the performance metrics of several Azure database service components such as Azure SQL databases, Azure elastic pooled, and managed instances. You can manage the data collection across multiple subscriptions. It provides a platform to collect, analyze, and visualize the database performance metrics that are helpful for performance troubleshooting and reporting purposes.

Operations Management Suite (OMS)

OMS is a management offering that provides a framework for monitoring Azure Resources using the Log Analytics service.

Log Analytics service provides a platform to the users to collect metrics, correlate, and visualize structured-and-unstructured data. Also, OMS Log Analytics—users can monitor Azure resources and use inbuilt alert alerting and notifying the health of the Azure resources.

Using OMS Log Analytics services—users can easily build custom solutions to monitor the health of the resource and define custom rules in addition to rules provided with the Analytics services. It also enables us to monitor multiple Azure subscriptions and their associated resources.

This preview feature supports up to 150,000 Azure SQL databases and 5,000 SQL Elastic Pools.

Installation overview

Let us configure the streaming the diagnostic telemetry into the Log Analytics Service You can configure the diagnostics settings using the Azure Management Portal or  PowerShell cmdlets, or Azure CLI, or Azure Monitor REST API, or Resource Manager templates.

To create an Azure SQL Analytics resource, follow the below steps:

  1. Open the Azure Portal Management Console
  2. Select the Create a resource icon

    Create Azure Resource

  3. In the Azure Market place offering, type in or search the keyword Azure SQL Analytics

    Create Azure SQL Analytics

  4. Select Azure SQL Analytics (Preview)

    Select Analytics icon

  5. Select Create. In the same pane, you’ll see a lot of additional educational materials to go over this feature. You can click watch Azure SQL Analytics — 5 minutes video that will give the high-level details

    Select Analytics preview option

  6. Select the option “Log Analytics Workspace”

    Create Analytics Solution

  7. Choose Create New Log Analytics Workspace. Type in the Azure SQL Analytics name, subscription, resource group, location, and pricing tier and click OK

    Create Log Analytics workspace

  8. Select OK and then select Create

    Create Log Analytics workspace

    You can see the deployment successful in the following snippet

    OMS status update pane

Configure the diagnostic telemetry

In this section, you will see how to configure diagnostic for SQL databases or elastic database or managed instances. It is very easy to configure and gather the metrics using the Azure portal.

In this exercise, you’ll see azuresqldemp1234567 instance created with multiple databases. Let us go ahead and plug diagnostic telemetry into the OMS workspace ASAnalytics.

Let us connect to Azure SQL databases to list the Azure SQL databases.

List Azure SQL database

Next, Open the Azure Management Portal and Browse Azure SQL Database instance azuresqldemo1234567

After selecting the Azure SQL Instance, locate the demodb azure SQL database. Choose the database where you need to configure diagnostic telemetry and send the logs into the OMS workspace.

List Azure SQL DB

After selecting the Azure SQL database demodb123789, scroll down on the left blade and locate the Monitoring pane.

Click Diagnostic settings and click “+Add diagnostic setting” on the right blade of the Azure Management Portal.

Diagnostic settings

Next, enable diagnostics and send telemetry data into the Log Analytics workspace.

  1. Type in the Diagnostic setting name. In this case, AzureSQLAnalytics-DevGroup is the name of the diagnostic setting
  2. In the log Analytics workspace drop-down, select the ASAAnalytics workspace
  3. Now, click the Save button to save the settings

Diagnostic settings - gather metrics

Let us take a look at the SQL Analytics category. The category details give you an option to select the log type and the data type that you need to send to the target.

The list of categories and log type is different, it varies between each Azure service. For example, you can see the following log categories in Azure SQL databases:

  1. SQLInsights
  2. Automatic Tuning
  3. QueryStoreRuntimeStatistics
  4. QueryStoreWaitStatistics
  5. Errors
  6. DatabaseWaitStatistics
  7. Blocks
  8. Timeouts
  9. Deadlocks

For Azure SQL Managed Instances:

  1. ResourceUsageStats
  2. DevOpsOperationsAudit
  3. SQLSecurityAuditEvents

List Server diagnostic setting

  • Note: If you do not have an OMS workspace, create a new workspace before you proceed with the configuration.

All diagnostics data (telemetry data) send to the Azure OMS workspace. Internally, the metrics are sent only to the Azure Monitor metrics time-series database.

The following table gives an insight into the log category and metrics that are captured for Azure SQL database and Azure SQL Managed Instance.

Telemetry Category

Azure SQL Database

Azure SQL Managed Instance

Basic metrics:

  • DTU/CPU percentage
  • DTU/CPU limit
  • Physical data read percentage
  • Log write percentage
  • Firewall connections
  • Sessions percentage
  • Workers percentage
  • Storage
  • Storage percentage
  • XTP storage percentage

Y

N

Instance Level

  • Tempdb usage (data and Log)
  • Tempdb log file percentage used

Y

N

QueryStoreRuntimeStatistics

  • Query runtime statistics
  • CPU usage
  • Query duration statistics

Y

Y

QueryStoreWaitStatistics:

  • Query wait statistics

Y

Y

Errors

  • It gathers SQL error information

Y

Y

DatabaseWaitStatistics

  • Database wait statistics
  • Database wait types

Y

N

Timeouts

  • Timeout details are captured

Y

N

Blocks

  • List blocking events

Y

N

Deadlocks

  • List deadlock events

Y

N

AutomaticTuning

  • Contains intelligent insights about telemetry information
    about automatic tuning recommendations

Y

N

SQLInsights

  • Contains Intelligent performance metric Insights

Y

Y

Query Log Analytics

In this section, you will see how to query the database in the Log Analytics workspace using a query language called Kusto Query Language (KQL).

  1. Browse the database and select the logs in the left blade
  2. In the SQL database, select the table AzureDiagnostics and run the command

Kusto Query run space

You can also see a conditional clause in the KQL query. I will discuss more about Kusto queries in my upcoming article.

To list all the diagnostics setting, select the command and Run as shown below:

AzureDiagnostics

Now, you can list the details with a conditional clause using Kusto Query Language as shown below:

AzureDiagnostics
| where OptionName_s == “CREATE_INDEX”

Summary

The future for Analytics is bright. We discussed the concepts of Azure SQL Analytics in this article. Also, we covered a step by step procedure to set up an Analytics solution. In addition, we observed, how easy it is to configure and deploy an analytics solution that delivers insights to the teams which need to make faster decisions at an incredible value.

In the next article, you’ll see more about Kusto Query Language and Intelligent Insights.

That’s all for now…stay tuned for more updates in my next article.

Prashanth Jayaram
Azure, Data science, SQL Azure

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

976 Views