Is Azure SQL Analytics all you need for SQL Server Monitoring?

How well can you monitor Azure SQL Databases and Azure Managed Instances without any 3rd party tools? Is Azure SQL Analytics really good enough for the job? Can it be a decent competitor to powerhouses such as SentryOne, Red-Gate, Apex, Solarwinds and others? Can you use it as a cheap alternative in the cloud? Will I ever stop asking questions and get to the point already? Yes, of course I will! Just keep reading.

What is Azure SQL Analytics?

Azure SQL Analytics is a “solution” that can be “installed” on an Azure Log Analytics workspace (recently renamed to “Azure Monitor logs”). It’s basically a large collection of dashboards, charts, and different kinds of reports that come together to give you a complete view of your Azure SQL activity and performance.

The solution supports the following Azure SQL resource types:

In a way, Azure SQL Analytics is a SQL Server monitoring and diagnostics platform that is very similar to other 3rd party flagships on the market, such as those from Quest, Apex, Red-Gate, SentryOne, etc., except that it’s all right there in your Azure cloud subscription.

Is it any good?

First thing first, as I said, Azure SQL Analytics only supports the managed SQL resource types in Azure. So, if you have SQL Server installed on a VM (IaaS), or on-premise (outside of Azure), then you won’t be able to use it. If that is your case, you’d have no choice but to go with a 3rd party tool instead.

But, if you’re already using Azure SQL DB or Managed Instance, then why not take full advantage of it? Also, Azure SQL Analytics is free, except for the cost of data ingestion and retention in Log Analytics.

But how is it compared to 3rd party platforms?

Azure SQL Analytics is quite good for, let’s call it, the “basic needs” of SQL monitoring and diagnostics. There’s a decent collection of diagnostic data out-of-the-box such as query wait stats, SQL errors, deadlocks, blocking queries, and even SQLInsights (full list here).

You can even set up automated alerts using Log Analytics queries (although those come at some added cost).

There’s also the added benefit of having everything consolidated in your Azure cloud subscription, where you can set up automated deployment using ARM templates, and not having to fuss around with 3rd party vendor licenses.

But… There’s a but. Azure SQL Analytics does have rather significant disadvantages:

Unpredictable Cost

Its cost can get quite unpredictable. It would depend on the amount of events collected from your databases, which would affect the ingestion and retention costs in Log Analytics. And it would depend on your usage of the alerts in Azure Monitor. So, the more activity you have in your databases, the more you gotta pay extra. It would probably be cheaper in most cases, but there’s definitely some kind of “tipping point” when comparing to 3rd party solutions (which tend to have a fixed price regardless of activity).

No Customization

You get a basic set of diagnostics in Azure SQL Analytics and that’s it. There is absolutely zero room for customization in terms of what you can collect and monitor. So, you can forget about monitoring results of custom queries, or implementing your own KPI / metrics… Well, not unless you feel like also implementing your own Log Analytics solution (but then it wouldn’t be Azure SQL Analytics so I guess that’s irrelevant).

No Query Text Until it’s Too Late

Azure SQL Analytics uses Query Hashes to identify and track SQL statements. This is the same Query Hash used in the Query Store (QDS). But this also means that the query text will NOT be immediately visible in the dashboards, and you would NOT be able to search on it – unless you already know the relevant Query Hash. You won’t see the query text itself until you drill-down to a specific query hash. Only then Azure SQL Analytics polls the target database for the relevant query text based on its hash and shows it to you. This can become very limiting and frustrating at times.

Limited Alerting Mechanism

Azure SQL Analytics does not come with its own alerting system. You will have to rely on the regular Azure Monitor of Log Analytics. This can be very limiting at times. If E-mail alerts are good enough for you then you’re in luck. But, if you need to have complicated integrations with 3rd party tools such as PagerDuty, Slack, Jira or whatever, and/or need to implement some kind of complicated logic to determine when to alert or not… Then you’re gonna have a fair share of trouble here.

It’s Just the Bare Minimum

In case it’s not clear enough yet, Azure SQL Analytics is basic. It’s the very bare-bone-minimum of what a production DBA may need from a SQL monitoring solution. Therefore, it’s missing some of the “cool” features that other 3rd party solutions may offer, such as incident / alert management, or proactive best practice advisories, or built-in index maintenance, or machine-learning anomaly detection of performance, and so on, you get the idea. It’s got a pretty good Root Cause Analysis mechanic via SQL Insights, but that’s about it. The rest is very standard stuff.

Also, its GUI is usable, but it’s definitely not the easiest to maneuver. You will probably find yourself drilling-down and up and sideways quite a bit until you find what you’re looking for.

Azure SQL Analytics is definitely NOT up to par with 3rd party monitoring solutions in the market.
But, I’d dare say that it’s at least better than all the free and/or open-source alternatives that I know of.

How to Set Up Azure SQL Analytics?

If you decided that Azure SQL Analytics could still be a good fit for you, then see below the steps needed in order to get started with Azure SQL Analytics, summarized in the most concise way possible:

1. Enable Azure SQL Analytics from the Marketplace

fig 1: type "azure sql analytics" in the search bar, and find it under the "Marketplace" section
fig 1: type “azure sql analytics” in the search bar, and find it under the “Marketplace” section

When enabling Azure SQL Analytics, all you’d need to do is choose which Log Analytics Workspace you want to install it on:

fig 2: Select an existing Log Analytics workspace, or create a new one
fig 2: if you don’t already have an existing Log Analytics workspace, you’d be asked to create a new one

2. Start Sending Diagnostics to the Log Analytics Workspace

Once you got a Log Analytics workspace ready, it’s time to start filling it with diagnostics data from your Azure SQL resources.

In each of your Azure SQL resources, be it an Azure SQL DB, Managed Instance or Elastic Pool, go to the “Diagnostic settings” menu item under the “Monitoring” section:

fig 3: In "Diagnostic settings" click on "Add diagnostic setting" to configure the collection of data
fig 3: click on “Add diagnostic setting” to configure the collection of data

Select which details you want to collect, enable “Send to Log Analytics” and choose the Log Analytics workspace that you previously created (the one with the Azure SQL Analytics solution):

fig 4: select all categories and select the log analytics workspace as a destination
fig 4: collect all the things!

3. Send Security Auditing to the Log Analytics workspace

You don’t have to do this step, but you can also send security auditing data into the same Log Analytics workspace, to get a more complete view of your DB activity:

fig 5: go to "Auditing" under the "Security" section, turn it on and choose your Log Analytics workspace as a target
fig 5: go to “Auditing” under the “Security” section, turn it on and choose your Log Analytics workspace as a target

And that’s it!

Yes, I’m serious. That’s really all there is to it. Just repeat steps 2 and 3 for each of your Azure SQL resources, and the cloud will do the rest! (Note: If you’re using Azure SQL Databases, you’d have to set it for each of the single databases)

How to Use Azure SQL Analytics?

I’m glad you asked! Because it’s actually super simple!

1. Find your Log Analytics workspace in the portal

fig 6: search for "Log Analytics workspaces" to get all the log analytics workspaces in your account, or type the name of the workspace if you know it
fig 6: you can search for all of your “Log Analytics workspaces”

2. Open the Workspace Summary

fig 7: you can either click on "Workspace summary" in the menu, or on "view solutions" in the main resource page
fig 7: you can either click on “Workspace summary” in the menu, or on “view solutions” in the main resource page

3. Open up the Azure SQL Analytics Solution

fig 8: you should find both the Security Insights and the Azure SQL Analytics solution in the overview page
fig 8: you should find both the Security Insights and the Azure SQL Analytics solution here

4. Start Analyzing!

Now just go wild!

Every single chart and table that you see here is clickable for a drill-through:

fig 9: This is what it looks like with a couple of Azure SQL DBs: Metrics per DB, resource utilization, query durations, wait stats, errors, deadlocks, timeouts and more
fig 9: check out this beauty. this is what it looks like with a couple of Azure SQL DBs
fig 10: this is what it looks like with an Azure Managed Instance: CPU utilization, query durations, wait stats, errors and SQL insights
fig 10: this is what it looks like with an Azure Managed Instance
fig 11: Click on the query wait stats chart and get this drill-through, with breakdowns per server, database, and query hash
fig 11: I just clicked on the query wait stats chart and got this drill-through
fig 12: Click on one of the query hashes and you'll see performance data just for that query: Durations, wait stats, CPU and IO utilization
fig 12: I just clicked on one of the query hashes and now I can see performance data just for this query!
fig 13: after clicking one of the query wait graphs, we get this Kusto / Log Analytics query which we can also customize and save and even create an alert for!
fig 13: after clicking one of the query wait graphs, I get this Kusto / Log Analytics query which I can also customize and save and even create an alert for!

Conclusion

To summarize, Azure SQL Analytics is a useful set of dashboards and reports for any production (or accidental) DBA with Azure SQL Databases or Managed Instances under their responsibility.

If what you need from your Azure SQL monitoring is just the basic stuff, then Azure SQL Analytics is probably the right tool for you, and it’s super-easy to set up and get started with.

But, if you need something more, like 3rd party integrations, or an alerting platform with something more complicated than “send me an e-mail”…. Then you should be thinking twice here.

Additional Resources

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s