Azure SQL Analytics: A Comprehensive Tutorial 101

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. 

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. 

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. 

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

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

Step 5: Click the “Create” button. 

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

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. 

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

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

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. 

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. 

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.

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