For production workloads running on your Azure SQL Managed Instance it is very important to continuously understand the resource consumption and any potential performance issues such that you can react in time and have your business running smoothly. There exist a number of options for monitoring and alerting of managed instances that can be considered, be it Microsoft cloud native, on-premises products, custom solutions, or third party solutions.
In understanding and evaluating these options there are several things you need to take into consideration depending on our workload and how critical to you are the apps you are running with managed instance. Some of the considerations you need to evaluate while deciding on a monitoring and alerting system for your managed instance are:
- Telemetry latency - how long it takes for the telemetry to reach you from the database until an alert or a notification is triggered (e.g. near real-time, each minute, or every several minutes).
- Sampling rate - how frequently is telemetry being sample from the database (e.g. every second, every minute, or every several minutes)
- Alerting capability - what are all the ways you can be alerted and if there are integrations point available (e.g. email, voice call, SMS, web hooks, integration with a 3rd party ticketing system, etc.)
- Resource cost - what is the impact of telemetry pooling to the performance of your database
- Skill set required - how easy or complex is it to setup a solution. (e.g. could a novice user set it up, or do you need DBA \ dev. skills)
- Cost - important as well is the financial cost for monitoring and alerting in terms of licencing, subscriptions, cost per alert, that is what is the price vs. performance ratio you need for your business continuity.
Monitoring solutions available today use two main ways to obtain telemetry from databases. The first is through Azure monitor pipeline. The telemetry is built at the backend and and there is no "tax" on the database performance. Monitoring solutions relying on this type of telemetry are Azure Monitor and Azure SQL Analytics. It is also possible to stream the database monitoring telemetry to Event Hubs and Azure Storage for custom integrations. The other way obtain telemetry for monitoring is using DMV querying of system views which has "tax" on the database performance, depending how often data is pulled from a database, and using XEvents which use Azure storage as an intermediary to store event data. All non-Azure cloud solutions rely on this type of telemetry collection, and these are SCOM + Management Pack (MP) for MI, Telegraf + Grafana open source solution, SSMS tool and third party solutions. This is depicted with the diagram below.
SQL Insights as the latest addition to the monitoring portfolio is the only solution at this time capable to harvest both Azure Monitor telemetry, and direct DMV queries to the database.
The following table provides summary of options available today to monitor managed instance resources and performance taking the above stated objectives into consideration.
|
Product |
Best used for monitoring |
Data sampling rate |
Alerts |
Uses DB resources for monitoring |
Skill set required to setup and use |
Pros |
Cons |
Cost |
|
(GA)
|
- Built-in monitoring of basic MI telemetry (CPU, storage, IOPS) |
- Sampling frequency: 1 minute |
- Yes (through Metrics chart in Azure portal) |
- No - Does not consume DB resources for monitoring (telemetry is built into the engine)
|
- Light technical skill set required to setup and use |
- Easy to use in Azure Portal out of the box |
- No multi-resource alerts (e.g. you cannot have 1 alert for many resources, but 1 alert for each resource in Azure) |
- Charged per single alert per month, see Azure Monitor pricing for details |
|
(preview) *NEW 2021
|
- Hybrid monitoring of all Azure SQL deployments. This includes monitoring PaaS services (Azure SQL Database, Managed Instance), and IaaS services (SQL Server 2012-2019 in Azure VM) |
- 15 seconds, or higher, user customizable |
- Yes, fully integrated with Azure Monitor and Azure Monitoring Alerts |
- Yes - The solution uses queries to pull the telemetry from the database. CPU overhead might be 0-3%. Users can configure resource governance through T-SQL. |
Light technical skill set required to monitor and troubleshoot performance - Moderate DBA technical skill set required to setup custom queries and alerts
|
- Out of the box solution integrated in Azure portal |
- 1 monitoring VM can support up to 100 resources (100 Managed Instances with all databases, or 100 Azure SQL Databases) |
- Charged for 3 components, based on usage: 1. Data ingested into Log Analytics 2. Alert rules based on log data. See Azure Monitor pricing for details |
|
(preview) |
- Monitoring at scale (large number of MIs and DBs) when real-time is not required. - Automatic performance troubleshooting with use of Intelligent Insights (enable SQLInsights log) - RAW monitoring telemetry is available for analytics for up to 30 days |
- Telemetry has about 10 minutes lag - See the supported diagnostic telemetry for databases and for the instances monitored. |
- Yes, but no presets - Customer needs to write own alerting queries. Examples here. |
- No - Does not consume DB resources for monitoring (telemetry is built into the engine) |
- Light technical skill set required to monitor and troubleshoot performance - Moderate DBA technical skill set required to setup custom queries and alerts |
- Out of the box solution on Azure - Monitor multiple Managed Instances and databases in a single place - Easily troubleshoot performance issues |
- Customization requires technical skill set - Alerting requires technical skill set - Solution is in preview |
- Cost: 5 GB / month of data ingestion is free (about 50 instance databases monitored for free), after that it is at cost.
|
|
(GA) |
- Support for legacy systems – best for companies already using SCOM, or for hybrid on-prem. and cloud monitoring |
- Sampling frequency manually set (typically each 5-15 minutes) |
- Yes, out of the box |
- Yes - Consumes DB resources for monitoring (queries executed on the DB for monitoring) |
- Moderate DBA skill set required to setup and use |
- Use existing SCOM on-premises systems for monitoring (familiar environment) |
- Customers need their own on-prem. installation of SCOM environment* (SCOM team is considering making the solution PaaS in the future) |
- SCOM system comes at cost of its own - SCOM MI MP (management pack) is a free add-on |
|
(open source) |
- When (almost) real-time monitoring is required - When cost is also a concern |
- Real-time - Sampling frequency manually set |
- Not out of the box - Possible with custom development only |
- Yes - Consumes DB resources for monitoring (queries executed for monitoring) |
- Moderate DBA technical skill set required to setup |
- Open source, open for customization |
- Manual setup required. - There is no support provided for this solution – customer is responsible for implementation. |
- Free |
|
(GA) |
Basic resource consumption, such is CPU utilization. Query execution stats. |
When refreshed by user |
No |
Yes, it uses DMV querying |
- Moderate DBA skill set required to setup and use |
- Use existing SSMS tool as with on-prem. SQL |
- Limited amount of telemetry available |
- Free |
|
DMV + xEvents + Agent (custom development) |
- When (almost) real-time monitoring is required - When custom alerting is required |
- Real-time - Sampling frequency manually set |
- Not out of the box - Possible with custom development |
- Yes - Consumes DB resources for monitoring (queries executed for monitoring) |
- Advanced DBA technical skill set required to setup |
- Limitless possibilities - Custom emails on alerts can be sent from Managed Instance using the built-in SQL Server Agent Mail |
- Customer needs to custom develop the code - There are no instructions and support provided for this solution – customer is responsible for implementation. |
- Free |
|
|
- When (almost) real-time monitoring is required - Allows access to raw performance telemetry - Consumed through many tools, such is for example SSMS
|
- Real-time - Sampling frequency manually set
|
- Not out of the box - Possible with custom development or 3rd party tooling
|
- No - Does not consume DB resources for monitoring (telemetry is built into the engine)
|
- Advanced DBA technical skill set required to use |
- Limitless possibilities with raw performance data - Same experience required as for SQL Serer |
- Some degree of technical knowledge required to use it |
- Free
|
|
3rd party solutions (GA): *see the note below |
- Customer would need to make an evaluation on their own |
|
|
|
|
|
|
- At cost charged by 3rd parties |
*While an attempt was made to list known 3rd party solutions this list might not be comprehensive as Microsoft is not responsible for 3rd party solutions. Other third party tools may add support for MI over time. Customers are advised to conduct research on their own.
Use one of the above links provided in the table to learn more about each individual product and option presented.
Disclaimer
Please note that products and options presented in this article are subject to change. This article reflects the state of monitoring options available for Azure SQL Managed Instance in May, 2021.
Closing remarks
If you find this article useful, please like it on this page and share through social media.
To share this article, you can use the Share button below, or this short link: http://aka.ms/mi-monitoring