Maxime Drobot

1 April 2021

• Redgate Monitor
• Monitoring Cloud/Hybrid SQL Servers

Monitoring Azure SQL Managed Instances

When Microsoft first released Azure SQL Managed Instances in 2018, SQL Monitor was one of the first monitoring tools to offer light support for this new platform-as-a-service (PaaS) option. Since then, SQL Monitor has added many new features, improved its performance and expanded its support to new platforms, including full support for Amazon RDS and now Azure SQL Managed Instances.

Adding Azure SQL Managed Instances

You can add Azure SQL Managed Instances to SQL Monitor either manually through the Configuration section of its user interface, or automatically, through its PowerShell API. You will need to provide the Azure SQL Managed Instance host name and the SQL Server credentials for accessing the Azure SQL Managed Instance

Once the Azure Managed Instance has been added, SQL Monitor will automatically collect and store all the key metrics, display the related data in graphs and alert on abnormal values or performance.

Performance and activity monitoring for Azure SQL Managed Instances

For each Azure SQL Managed Instances that you add, SQL Monitor will collect a built-in set of metrics and a configurable set of built-in alerts. It will collect this data both from your SQL Server instances and databases.

Monitoring your SQL Server instances, regardless of where they are hosted, requires a similar set of metrics and alerts, and SQL Monitor aims to collect a standard set of diagnostic data across all of them, as far as is possible. Of course, there are inevitable omissions and differences, particularly for machine-level metrics, depending on which ones the hosting service make available. Sometimes there is also a requirement for ‘specialist’ metrics, tailored for the monitoring requirements of a particular platform (such as monitoring DTUs for Azure SQL Databases and Elastic Pools).

For Azure SQL Managed Instances, there are some enforced differences at the machine/server level, which I’ll discuss shortly, but beyond that the Server Overview page for an Azure Managed Instance will instantly look familiar, with access to many of the same sections and information as for any on-prem SQL Server. More details about what we capture can be found in our documentation, including:

• Server/Host metrics
• Tempdb metrics
• Blocking processes
• SQL User processes
• Processes
• Error log
• Database-level information

In the Server/Host metrics section, you’ll see that SQL Monitor collects execution statistics and query plans, from the dynamic management objects, for any significant queries running on your Azure-hosted databases. This means can focus your tuning efforts on the longest running queries, or those queries that are the highest consumers of limited compute resources, such as IO and CPU. In managed services, if the IOPS or throughput from your workload approaches or exceeds the limits of your chosen storage tier, you’ll see ‘throttling’ and performance problems. A tool like SQL Monitor is going to help you avoid that sort of difficulty, especially with its ability to plot baselines and perform trend analysis, for these metrics. SQL Monitor also collects wait statistics so that you start to see which resources your queries are commonly waiting to acquire access to.

It also provides metrics for blocking processes (including the blocked process report), and SQL user processes, so you can diagnose problematic processes that cause frequent delays in response times, due to blocking, and see which users and applications are running them. SQL Monitor also collects a detailed set of tempdb metrics for troubleshooting contention issues on this shared resource.

Figure 1-Azure MI dashboard showing tempdb, blocking processes and SQL user processes information

You’ll also see detailed database level metrics, for every database hosted on an Azure-Managed Instance:

Figure 2-Azure MI dashboard showing database level information

Server metrics in Azure SQL Managed Instances

One advantage of using Azure Managed Instances is that Microsoft looks after the machines. This means that you will not necessarily need, or even be able, to collect machine and OS-level data as Microsoft estimate that this is not something you should worry about.

SQL Monitor collects CPU, Disk IO and server waits, just as it does for any on-premises instance, but it turns out that the machine’s physical memory capacity and the Operating System’s reported free memory are not readily available for Azure SQL Managed Instances. Therefore, no monitoring tool can provide that information.

Figure 3 – Server metrics for Azure Managed Instance, without memory information

Monitoring hybrid estates using SQL Monitor

Although migration to the cloud and usage of PaaS solutions have accelerated in the past years, pure Azure estates are still rare and most organizations run a mixture of on-prem servers and cloud-hosted-instances, including Azure SQL databases, Azure SQL Managed instances, and Amazon RDs-hosted instances.

To support these business needs and ensure the availability, security and performance of databases hosted across a range of different platforms, the DBA needs to be able to monitor all types of instances in the same monitoring tool, with a consistent approach, and minimum effort.

SQL Monitor’s global dashboard summarizes the status of all your servers and instances, regardless of the platform hosting them. Further, all the features of SQL Monitor, such as organizing monitored servers into groups, as well as alert management and alert notifications across all of these groups, remain as similar as possible across platforms (with small divergences for some of them due to technical differences, such as between Azure and on-prem).

Figure 4- SQL Monitor Global Dashboard showing on-premises and PaaS servers

By keeping the user-experience consistent for Azure/on-prem hybrid estates, SQL Monitor allow DBAs to focus on responsiveness, improving performances and supporting business-critical areas.

Migrating from on-prem to Azure Managed Instance

When moving from on-prem to Azure Managed Instance, monitoring needs to be an integral part of the project. Before starting the migration, it is recommended to benchmark the current performance by monitoring the server which will be migrated and capture days’ worth of your performance metrics, such as disk transfers per second (IOPS) which are likely to change after moving to Azure. You should monitor the performance of your Azure Managed Instance immediately post-migration to ensure that the databases hosted in the cloud are available and running as expected.

Once you have completed your migrations you will have the benefit of monitoring your Azure Managed Instances in the same way as you have monitored your on-premises instances, bringing consistency and familiarity to you and your team’s work and avoiding time-consuming relearning of the tool.

You can get started with SQL Monitor by downloading a free trial, or check out our SQL Monitor demo for a walk-through of the fundamental features and benefits.