Azure SQL Analytics – Monitoring Azure SQL Databases & Azure SQL Managed Instances

Opening

In this article, I will show you how to set up your own Azure SQL Analytics, how to stream diagnostic telemetry from your Azure SQL databases and Azure Managed Instances to Azure SQL Analytics, and lastly, we’ll consider the pros and cons to help you decide if this solution is appropriate for your needs.

Disclaimer! Azure SQL Analytics has been in preview since it launched a few years ago. Microsoft’s documentation clearly states that no active development is done on this monitoring solution. Microsoft hasn’t provided an exact date when this product will be retired.

Middlegame

What is Azure SQL Analytics?

Azure SQL Analytics is a cloud solution provided by Microsoft that enables you to monitor multiple Azure SQL databases, Azure Managed Instances or elastic pools in a single view. Key metrics streamed from monitored databases are collected and displayed as a dashboard, making performance troubleshooting more efficient.

Create an Azure SQL Analytics solution

1. Log in to the Azure Portal.
2. Create a resource group to store your Azure SQL Analytics solution. In this example, I created a resource group named Monitoring.
3. In your resource group page, select +Create at the top of the page.

4. Search for Log Analytics Workspace in the Azure Marketplace and select +Create.

5. Choose a subscription, resource group, name and location for this resource, then select Review + Create.

6. After validation is confirmed, select Create.

7. Back in your resource group page, select +Create at the top.

8. Search for Azure SQL Analytics in the Azure Marketplace and select +Create.

9. Choose a subscription, resource group and the previously created log analytics workspace, then select Review + Create.

10. After validation is confirmed, select Create.

11. Both resources should now be visible in your resource group.

Stream diagnostic data from Azure SQL Databases to Azure SQL Analytics

For this tutorial, I’ve created a separate resource group called Databases, where I deployed 3 Azure SQL DBs, each hosted in a different region.

On each database, the same steps need to be followed:

In the Azure Portal, connect to your Azure SQL Database.
On the left menu, select Diagnostic Settings, then select + Add diagnostic setting.

3. On the Diagnostic setting page, configure as follows:

After you finish configuring the diagnostics on all databases, metrics will be streamed into the Azure SQL Analytics solution (it can take up to 90 minutes for data to appear in the dashboard). The following telemetry is available depending on your SQL resources:

Endgame

Now that we finished setting everything up let’s have a look at the Azure SQL Analytics dashboard:

In a single view, we can quickly explore metrics from all our monitored SQL resources, or we can drill down into any of the graphs for more details:

Why do I like Azure SQL Analytics?

- Relatively easy to set up and use

- Very cheap (the current test setup costs only 0,25 $/month for 3 databases)

- Overview of all monitored databases in the main dashboard

- Sufficient metrics to help diagnose the most common performance issues

- Information about timeouts and errors thrown by the database engine

Can run custom scripts on the streamed logs and configure custom alerts

Downsides of using Azure SQL Analytics

- It’s been in preview since it launched

- The documentation states that no active development is being done on it

- Can be retired at any time with short notice from Microsoft

For me, the benefits outweigh the drawbacks. That is why I actively use Azure SQL Analytics in combination with paid monitoring software to monitor production databases.

I hope this blog post has given you a better understanding of what is Azure SQL Analytics and how to set it up for your environment. Hopefully, it also helped you decide if this solution is worth your attention.