{"id":106091,"date":"2025-03-31T03:24:42","date_gmt":"2025-03-31T03:24:42","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=106091"},"modified":"2026-05-21T15:13:32","modified_gmt":"2026-05-21T15:13:32","slug":"managed-instance-gotchas-error-conditions","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/managed-instance-gotchas-error-conditions\/","title":{"rendered":"5 key problems from a SQL Server to Azure SQL Managed Instance migration (and how to avoid them)"},"content":{"rendered":"\n

Azure SQL Managed Instance (MI) sits in a useful middle ground. It has most of the functionality of a full on-premises SQL Server, with Microsoft handling the engine, backups, OS, and hardware. Plus, it supports cross-database queries and SQL Agent jobs, with fewer limits than Azure SQL Database – making it popular for migrating transactional workloads.<\/strong><\/p>\n\n\n\n

The migration itself isn’t seamless, however. This article reveals the five surprises I encountered from a real production SQL Server to Azure SQL MI migration<\/strong> – and what I learned from them. Includes guidance and advice to make your migration as seamless as possible.<\/strong><\/p>\n\n\n\n

A bit of background<\/h2>\n\n\n\n

I was recently on a project to migrate a very transactional installation of SQL Server to Azure SQL Managed Instance (MI). SQL Managed Instance is a good stepping stone between a full, on-premises SQL instance \/ Azure VM and an Azure SQL Database. It has most of the functionality of a full, on-prem instance, with management of the SQL engine, backups, OS and underlying hardware done by Microsoft. It allows you to use cross database queries and run SQL Agent jobs, with fewer limitations than Azure SQL Database migrations.<\/p>\n\n\n\n

The migration process isn\u2019t completely seamless. During the migration of this system, we encountered several surprises – five of which are detailed in this article. Hopefully, this will help you avoid, or at least be prepared for these differences from the on-prem version. This also reinforces the importance of testing each aspect of your migration.<\/p>\n\n\n\n

The 5 key problems I encountered – and what I learned from them<\/h2>\n\n\n\n

There’s no support for DNS aliases<\/h3>\n\n\n\n

If you plan on using DNS aliases to assist the transition to managed instances, know that it isn\u2019t supported. There may be some way to hack a solution, but that\u2019s generally not a good idea. We tried to use aliases to allow clients\/ external systems to make the DNS name transition before our production migration. <\/p>\n\n\n\n

To put it simply, this was a failure. The Azure environment blocks these aliases. You can only use the Microsoft assigned DNS entry. There are likely also certificate issues if using your own alias. You may be able to hack some solution, but that would be fragile at best, and unsupported.<\/p>\n\n\n\n

Plan on client applications migrating at the same time as your server migration. In a best-case scenario, the number of clients hitting your system directly will be limited. But be ready for this coordination when you migrate.<\/p>\n\n\n\n

Zone redundancy<\/h3>\n\n\n\n

An attractive aspect of migrating to MI is the high-availability options available. Zone redundancy synchronizes your server between zones in Azure (refer to the references for detailed zone redundancy information). This is the high-availability option. If a datacenter has issues, your server will fail-over to another zone in your region, when using zone redundancy.<\/p>\n\n\n\n

We had significant problems with zone redundancy. After our initial deployments, we enabled zone redundancy in our dev environment. This was successful. After testing, we tried to enable zone redundancy in production. Because server and CPU resources weren\u2019t available in this region (during this deployment), all of our deployments failed. Even environments that were established failed to deploy with no changes.<\/p>\n\n\n\n

After many discussions internally, talking to our Microsoft liaisons, and opening a Microsoft ticket, we found that the documentation was incomplete. Zone redundancy was not available in our region and likely will not be available until the 4th<\/sup> quarter of this year. We had this error even though we had successfully enabled zone redundancy in one of our environments.<\/p>\n\n\n\n

This is error we saw in our DevOps environment during our attempted deploy:<\/p>\n\n\n\n

{\u201ccode\u201d:\u201dManagedInstanceZoneRedundantFeatureTemporarilyDisabledPerHardwareGeneration\u201d,\u201dmessage\u201d,\u201dCreation of ZoneRedundant SQL MI is temporarily disabled for this hardware generation on this region. You can create Zone Redundant SQL Managed Instances with different hardware generation on this region or many other regions in Azure...\u201d}<\/pre>\n\n\n\n
The lesson learned from this is, confirm that the options you want to use are available before you plan your migration. The documentation is incomplete. Demand for services in Azure is very high and documented resources may be inaccurate. Consult your Azure representative to verify your plan. Even if it works once, you may be unable to deploy later due to these limitations.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Fast, reliable and consistent SQL Server development…<\/h3>\n                
\n                                            …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
TDE-enabled databases<\/h2>\n\n\n\n
Moving to Azure Managed Instances is a commitment. If you want to have all of the benefits of a managed server, that probably includes having Microsoft manage the keys for your TDE (encryption at rest) enabled databases. The initial transfer of your databases is a little complicated if it\u2019s TDE enabled. You either need to manage your own keys, which complicates things considerably in the long run, or make the switch to Microsoft managed keys.<\/p>\n\n\n\n
If you continue to use your own keys, you need to manage them in Azure SQL MI. This negates some benefits of migrating to MI (primarily, key management and unmanaged PITR restore options between environments). Assuming you don\u2019t want to manage keys, the steps to migrate a TDE enabled database are the following:<\/p>\n\n\n
\n