Monitoring options available for Azure SQL Database Managed Instance

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

For production workloads running on your Azure SQL Database 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.

 

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

 

Azure Monitor 

(built in the portal)

- Built-in monitoring of basic MI telemetry (CPU, storage, IOPS)

- Sampling frequency: 1 minute

- No (*Alerts from the portal are expected to become available in January 2020)

- 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 alerting out of the box available (expected to become available in January 2020)

- Free (for standard out of the box metrics)

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 a CTP (Community Technical 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

DMV + Agent + email

(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:

- Customer would need to make an evaluation on their own

 

 

 

 

 

 

- At cost charged by 3rd parties

Use one of the above links provided in the table to learn more about each individual product and option presented.

 

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 Database managed instance in December, 2019.

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

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