Monitoring options available for Azure SQL Managed Instance

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


 


Monitoring screenshot.png


 


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



Azure Monitor 


(built in Azure portal)


 



– 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



Azure SQL Analytics



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


 



SCOM + MI MP


(management pack)



– 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)



– Currently SCOM MP is in a public preview


– GA is expected in H2 2020 (non-committing)



– SCOM system comes at cost of its own


– SCOM MI MP (management pack) is a free add-on



Telegraf + Grafana 


(use open source solutions to monitor MI)



– 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



SSMS performance dashboard



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


(customers’ own 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 only.



– 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



– Complete custom development


– There are no instructions and support provided for this solution – customer is responsible for implementation.



– Free



3rd party solutions:



*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, 2020.


 


Closing remarks


 


To share this article, you can use this short link: http://aka.ms/mi-monitoring


Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.