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.
Therefore…
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
When enabling Azure SQL Analytics, all you’d need to do is choose which Log Analytics Workspace you want to install it on:
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:
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):
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:
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
2. Open the Workspace Summary
3. Open up the Azure SQL Analytics Solution
4. Start Analyzing!
Now just go wild!
Every single chart and table that you see here is clickable for a drill-through:
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.