Understanding Azure SQL Managed Instance Limitations

Azure SQL Managed Instance is a great option for businesses looking to migrate their databases to the cloud, but it's essential to understand its limitations before making the switch.

One key limitation is the requirement for a minimum of 8 vCores, which can be a barrier for smaller businesses or those with limited resources.

Azure SQL Managed Instance is not suitable for small databases, as it has a minimum storage capacity of 32 GB, which can be a significant overhead for small datasets.

Another limitation is the inability to use Azure SQL Database's free tier, which is a cost-effective option for small databases.

Azure SQL Managed Instance limitations can be a bit tricky to navigate, but don't worry, I've got you covered. Here are some known limitations to be aware of:

Differential backups aren't taken when an instance is linked to SQL Server, so you'll need to plan accordingly. This limitation was discovered in September 2024 and is considered a by-design feature.

Check this out: Azure Firewall Limitations

List of long-term backups in Azure portal shows backup files for active and deleted databases with the same name, which can lead to confusion. This issue was discovered in March 2024 and has a workaround available.

Temporary instance inaccessibility using the failover group listener during scaling operation is a known issue, but unfortunately, there's no resolution yet. This limitation was discovered in January 2024.

Here are some specific limitations to keep in mind:

Manual failover via portal for failover groups has a limitation: if a failover group spans across instances in different Azure subscriptions or resource groups, manual failover can't be initiated from the primary instance in the failover group. To work around this, initiate failover via the portal from the geo-secondary instance.

You can't lower the max server memory, so if you want to test how things perform with less memory, you'll need to use a different approach. This limitation is a result of the way Azure SQL Managed Instance is designed.

Curious to learn more? Check out: Management Group Hierarchy Azure

Some columnstore index features might not be available, but it's hard to tell if this applies to managed instance or if the documentation is up to date. Be sure to check the latest information before relying on these features.

Identifying log write limits on Azure SQL Managed Instance can be done using the QPI library, which can help you troubleshoot write log throughput issues. This is a useful tool to have in your toolkit when working with Azure SQL Managed Instance.

Changing the connection type of an instance in a failover group doesn't affect connections established through the failover group listener endpoint. You'll need to drop and recreate the failover group to apply the new connection type.

Manual failover via the portal is limited for failover groups spanning across instances in different Azure subscriptions or resource groups. This means you can't initiate failover from the primary instance.

To work around this limitation, initiate failover from the geo-secondary instance using the portal.

Expand your knowledge: Connection String for Azure Sql Database

Manual failover via the portal can't be initiated from the primary instance in a failover group that spans across instances in different Azure subscriptions or resource groups.

If you're dealing with such a scenario, you'll need to initiate failover from the geo-secondary instance to get around this limitation.

On a similar theme: Azure Sql Failover Group

Changing the connection type doesn't affect connections through the failover group endpoint. This means that if you change the connection type of an instance, it won't impact the connections made through the failover group listener endpoint.

If your instance participates in a failover group, changing the connection type won't take effect for the connections established through the failover group listener endpoint. This can cause confusion and unexpected behavior.

To work around this issue, you'll need to drop and recreate the failover group after changing the connection type. This will ensure that the new connection type is applied consistently across all connections.

Readers also liked: Azure Endpoint Manager

Cross-database Service Broker dialogs must be reinitialized after a service tier upgrade. This is because the dialogs will stop delivering messages to services in other databases after a change service tier operation.

The messages aren't lost, and they can be found in the sender queue. Any change of vCores or instance storage size in SQL Managed Instance causes a service_broke_guid value in sys.databases view to be changed for all databases.

To avoid issues, stop any activity that uses cross-database Service Broker dialog conversations before updating a service tier. Reinitialize them afterward to ensure messages are delivered correctly.

If there are remaining messages that are undelivered after a service tier change, read the messages from the source queue and resend them to the target queue.

Explore further: Azure Sql Cross Database Query

Database and storage limitations can catch you off guard, especially if you're not aware of them. General Purpose instances of SQL Managed Instance have a storage limit of 35 TB, which can be exceeded even if the total size of all databases doesn't reach the instance size limit.

Consider reading: Azure Blob Storage Limits

You can't create or restore new databases if there isn't enough space for new disk drives, even if existing databases can continue to work and grow. This can happen due to internal fragmentation, where a specific distribution of files can lead to the instance reaching the 35-TB limit.

Here's a simple example of how this can occur: one large file that's 1.2 TB in size placed on a 4-TB disk, and 248 files that are 1 GB each and placed on separate 128-GB disks. In this case, the total allocated disk storage size is 35 TB, while the total reserved space for databases is only 1.4 TB.

You might like: Azure Managed Disk Terraform

Small database files can cause issues with storage space. Each General Purpose instance of SQL Managed Instance has up to 35 TB of storage reserved for Azure Premium Disk space.

Database files are placed on separate physical disks, with sizes ranging from 128 GB to 4 TB. Unused space on the disk isn't charged, but the total sum of Azure Premium Disk sizes can't exceed 35 TB.

On a similar theme: Azure Storage Manager

A General Purpose instance might reach the 35-TB limit due to internal fragmentation, even if the total size of all databases doesn't reach the instance size limit. This can happen when a large file is placed on a 4-TB disk, along with many smaller files on separate disks.

For example, a single 1.2 TB file on a 4-TB disk, combined with 248 files that are 1 GB each on separate 128-GB disks, can exceed the 35 TB limit. In this case, the total allocated disk storage size is 1 x 4 TB + 248 x 128 GB = 35 TB, while the total reserved space for databases is 1 x 1.2 TB + 248 x 1 GB = 1.4 TB.

You can identify the number of remaining files by using system views. If you reach this limit, try to empty and delete some of the smaller files by using the DBCC SHRINKFILE statement or switch to the Business Critical tier, which doesn't have this limit.

CLR modules in SQL Managed Instance can't always reference a local IP address. This is a known issue.

A transient error can occur when CLR modules try to resolve the local instance's IP address. This is a temporary issue that can be resolved on its own.

CLR modules and linked servers or distributed queries that reference a current instance sometimes can't resolve the IP of a local instance. This error is a transient issue.

In Azure SQL Managed Instance, you can't have reasonably sized data and log files in the General Purpose service tier. This is because every database file gets dedicated IOPS and throughput that depend on the file size.

You'll have to create artificially large files, which can get weird and expensive fast. Paying for unnecessary storage is a real concern.

To get around this, consider using a different service tier that allows for more flexible file size management. Unfortunately, this isn't an option in the General Purpose tier.

Here's a comparison of the maximum resource limits for Azure SQL Database and Azure SQL Managed Instance:

As you can see, Azure SQL Managed Instance has more restrictive storage size limits than Azure SQL Database. This can make it harder to manage your database files in Managed Instance.

If you notice an increased number of system logins in your Azure SQL Managed Instance, it's likely due to transactional replication. These logins can be found in SSMS under Security, Logins or in the system view sys.syslogins.

The login name format looks like 'DBxCy\WF-abcde01234QWERT', and the login has public server role. They don't represent a security threat, so you can safely ignore them.

These logins shouldn't be deleted, as at least one of them is being used for transactional replication.

The maximum vCore memory limit for a single instance is 64 vCores, which is a significant improvement over the previous limit of 32 vCores.

See what others are reading: Azure Openai Rate Limit

This increased limit allows for more efficient use of resources, especially in large-scale database environments.

A single instance with 64 vCores can support up to 256 GB of memory, which is a substantial increase from the previous limit of 128 GB.

With this increased memory capacity, you can run more data-intensive workloads and achieve better performance.

In addition, the increased vCore memory limit enables you to run more instances in a single database, which can help to improve scalability.

Permissions on a resource group don't automatically apply to SQL Managed Instance, so you need to set up a SQL Managed Instance Contributor role at the subscription level.

Setting up roles at the subscription level ensures that permissions are applied correctly and have the desired effect.

See what others are reading: Azure Subscription Limits

Permissions on resource groups are not applied to SQL Managed Instances, even with the SQL Managed Instance Contributor Azure role assigned.

The SQL Managed Instance Contributor role has no effect when applied to a resource group, making it essential to set up this role at the subscription level.

A workaround is to set up a SQL Managed Instance Contributor role for users at the subscription level, ensuring they have the necessary permissions.

This issue was not resolved until August 2023, highlighting the importance of staying up-to-date with the latest security patches and updates.

Readers also liked: Azure Resource Manager

Service Principal Access to Microsoft Entra ID and AKV is a crucial aspect of Azure SQL Managed Instance security and permissions.

In some circumstances, Service Principals used to access Microsoft Entra ID and Azure Key Vault (AKV) services might not work as expected. This can cause intermittent connectivity issues or prevent statements like CREATE LOGIN/USER FROM EXTERNAL PROVIDER from running.

To prevent this issue, it's essential to verify if your SQL Managed Instance has a valid Service Principal set up to access Microsoft Entra ID. You can do this by going to the Overview page of your SQL managed instance in the Azure portal and checking the Microsoft Entra ID admin page.

If you encounter the error "Managed Instance needs a Service Principal to access Microsoft Entra ID", select it and follow the step-by-step instructions provided to resolve the issue.

Here are the error messages you might encounter when impersonating Microsoft Entra login types:

These error messages indicate that impersonation using EXECUTE AS USER or EXECUTE AS LOGIN is not supported for these types of Microsoft Entra principals.