Azure SQL Analytics: A Comprehensive Tutorial 101

on Data Analytics, Microsoft Azure, Tutorials • November 12th, 2020 • Write for Hevo

AZURE SQL ANALYTICS

Azure SQL Database is an intelligent, scalable, and relational database service developed for the cloud. It’s always up to date, with AI-powered features that optimize durability and performance for you. It can automatically scale resources on-demand so that users can focus on building new applications rather than resource management. Due to this, Azure SQL Database has become a data storage option for many companies. Companies can use it to store large volumes of data and get improved performance. 

When using Azure SQL Database, you will want to analyze your data to gain insights from it. You can then use the insights to make sound decisions regarding the running and management of your business. You will also need to monitor the performance of all your Azure SQL Databases. Azure SQL Analytics is the right tool for it. It makes it easy for you to analyze your data and monitor the performance of your Azure SQL Databases. In this article, you will be learning about the Azure SQL Analytics tool. 

Let’s see how this blog is structured for you:

  1. Prerequisites
  2. What is Azure SQL Analytics?
  3. Create an Azure SQL Analytics Resource
  4. Configure the Diagnostic Telemetry
  5. Query Log Analytics
  6. Limitations of Azure SQL Analytics
  7. Conclusion

Prerequisites

This is what you will need for this article:

  • Microsoft Azure account. 

What is Azure SQL Analytics?

Azure SQL Analytics is a cloud monitoring solution that can help you to monitor the performance of all your Azure SQL databases across multiple subscriptions in a single dashboard. It collects and analyzes the key performance metrics with built-in intelligence for troubleshooting the database performance. After collecting the metrics, you can come up with custom monitoring rules and alerts. 

Azure SQL Analytics can help you identify issues at every layer of your application stack. It uses Azure Monitor views, along with Azure Diagnostic metrics to present data about your databases in a single Log Analytics workspace. The Azure Monitor is a tool for collecting, correlating, and visualizing, both structured and unstructured data. In the next few sections, you will be configuring Azure SQL Analytics to stream the diagnostic telemetry into the Log Analytics Service. 

Hevo Data: Integrate your Data for Better Analysis

Hevo Data provides its users with a simpler platform for integrating data for analysis. It is a No-code Data Pipeline that can help you combine data from multiple sources. It supports pre-built integration from 100+ data sources(including 40+ free data sources). It provides you with a consistent and reliable solution for managing data in real-time, ensuring that you always have analysis-ready data in your desired destination. Your job will be to focus on key business needs and perform insightful analysis using BI tools. 

Get Started with Hevo for free

Let’s look at some salient features of Hevo:

  • Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
  • Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
  • Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
  • Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within pipelines.
  • Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support call.
Sign up here for a 14-day Free Trial!

Create an Azure SQL Analytics Resource

The following steps will help you create an Azure Analytics resource:

Step 1: Open the Azure Portal Management Console.

Step 2: Click the “Create a resource” icon. 

Azure SQL Analytics: Create new resource

Step 3: Type the keyword “Azure SQL Analytics” into the Azure marketplace offering. 

Azure SQL Analytics: Type Azure SQL Analytics:

Step 4: Select “Azure SQL Analytics (Preview)”. 

Step 5: Click the “Create” button. 

Azure SQL Analytics: Click Create

You will be provided with a lot of educational materials about this feature. 

Step 6: Select the “Log Analytics Workspace” option. 

Step 7: Select “Create New Log Analytics Workspace”. Type the Azure SQL Analytics name, subscription, resource group, location, and pricing tier.

Step 8: Select “OK” and then click “Create”. 

You will see a message stating that the deployment is complete. 

Configure the Diagnostic Telemetry

In this section, you will be configuring diagnostics for database instances. The Azure portal makes it easy for you to configure and gather the metrics. You will see an instance created with many databases. You will then plug diagnostic telemetry into the Operations Management Suite (OMS) Workspace. 

  • Connect to Azure SQL Databases and list the available databases. 
  • You can then open the Azure Management Portal and select the Azure SQL instance that you want to use. 
  • Select the database where you want to configure diagnostic telemetry and send logs into the OMS workspace. 
  • After selecting the database, scroll downwards on the left pane and find the Monitoring pane. 
  • Click “Diagnostic settings” and then select “+Add diagnostic setting”. 
Azure SQL Analytics: Add diagnostic

Now, it’s time to enable diagnostics and send the telemetry data into the Log Analytics workspace. Just follow the steps given below:

Step 1: Enter the name of the Diagnostic setting in the “Diagnostic settings name” box. 

Azure SQL Analytics: Give diagnostic settings name

Step 2: Select the ASAAnalytics workspace from the Log Analytics workspace drop-down. 

Step 3: Click the “Save” button to save the changes. 

Azure SQL Analytics: Click save

The category section allows you to choose the log type and the data type that you want to send to the target. The list of categories and log type will vary from one Azure service to another. 

Azure SQL Analytics: Category detail

All the diagnostics/telemetry data will be sent to the Azure OMS workspace. Internally, the metrics will only be sent to the Azure Monitor metrics time-series database. 

Query Log Analytics

In this section, you will know how to query the database in the Log Analytics workspace using the Kusto Query Language (KQL). Do the following steps:

Step 1: Browse the database and select “Logs” from the left navigation pane. 

Azure SQL Analytics: Select Log

Step 2: In the SQL database, choose the table with the analytics data. In my case, it’s the AzureDiagnostics table. Run the command:

AzureDiagnostics

To see all the diagnostics settings, select the command, and click “Run”. You can also query the details with a conditional clause using the Kusto Query Language, as shown below:

AzureDiagnostics
 where OptionName_s == “CREATE_INDEX”

Limitations of Azure SQL Analytics

Azure SQL Analytics is a good tool for performance monitoring. However, it is associated with the following limitations:

  1. Higher Cost for Data Ingestion and Retention in Log Analytics: Its costs are also unpredictable depending on the number of events collected from the database. The cost also depends on how you use alerts in Azure Monitor. The higher the number of activities in your database, the costlier it is.
  2. No Customization: Azure SQL Analytics only gives you a basic set of diagnostics. There is no room for customization regarding what you can collect and monitor. So, you won’t monitor results for custom queries or implement your own KPIs/metrics. 
  3. Limited Alerting Mechanism: Azure SQL Analytics doesn’t have its own alerting mechanism. You will have to use the regular Azure Monitor of Log Analytics. 

Conclusion

In this blog, you’ve learnt about Azure SQL Analytics, how to configure Azure SQL stream analytics the diagnostic telemetry into the Log Analytics Service and how to query a database in Log Analytics workspace using the Kusto Query Language (KQL). You also came across the various limitations of Azure SQL Analytics. In case, you want to integrate your data from various sources before analysis, then you should try Hevo Data.

Visit our Website to Explore Hevo

Hevo is a No-code Data Pipeline. It supports pre-built integration from 100+ data sources . Hevo allows consolidating your data from various sources and gives better analysis results.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

Share your experience of using Azure SQL Analytics in the comment section below.

No-code Data Pipeline for your Data Warehouse