[Pros & Cons] Azure SQL Managed Instances

As of this writing this, we don’t have a single client here at Red9 where we are using SQL Managed Instances. We do have some clients that have Azure SQL though.

Pros and cons

Here are a few notes I took at SQLSaturday Atlanta about Azure SQL Managed Instances.

Currently there are two managed DB options in Azure:

1. Azure SQL, and
2. Azure SQL Managed Instance

Azure SQL Managed Instance vs Single Database

Azure SQL Database

Pros

• Simple and easy to provision
• Most things are managed — like backups, HA, etc.
• Supports Serverless tier, which allows auto-pausing and cost savings for low-usage workloads

Cons

1. Backups are fully managed – great for compliance, but tricky if you want to log ship, perform manual restores, or export data easily
2. SQL Server Agent doesn’t exist – there is Elastic Jobs, but it’s not a full replacement
3. No cross-database communication
4. Can’t power down unless using Serverless – provisioned tier continues billing 24/7
5. Can’t restore native backups
6. To import, you have to:
   • Use Tasks > Export
   • Or script out all objects
   • Or use Azure Database Migration Service (DMS)

Use case that works well

1. When building a new app
2. Limitations make migrating existing apps difficult (for large, complex workloads, most clients just won’t go here)

Azure SQL Managed Instance – PROS & CONS

Pros

1. Offers the best of both PaaS and IaaS worlds
2. Infrastructure managed by Azure
3. No more Windows or SQL patching — nice!
   • But you can’t control when patching happens — Azure may reboot during business hours
4. Supports multiple databases
5. Supports cross-database queries
6. SQL Agent is available
7. Lift & shift migrations are possible
8. Backups are automatically managed, and now you can run ad hoc COPY_ONLY backups
9. COPY_ONLY backups can be run by ad hoc
10. Roughly 95–98% feature parity with on-prem SQL Server. Some missing features worth noting:
    • FILESTREAM
    • SSRS/SSIS direct integration
    • CLR with external access (limited)
11. Built-in High Availability and Disaster Recovery — no need for custom Always On config
12. If you already have core licenses (with Software Assurance), you can apply Azure Hybrid Benefit
13. If you have a Microsoft account manager, they may help expedite quotas or provisioning

Cons

1. You can’t shut it down — billing continues even when idle (no Serverless tier for MI)
2. In-memory filegroups have some limitations (though support has improved)
3. Initial deployment time has improved:
   • First MI in a subnet: around 4–6 hours
   • Additional MIs in the same subnet: around 30 minutes
4. High Availability is fully automated – you can’t configure things like AG roles or quorum settings
5. Passwords must be at least 16 characters long

Azure SQL Managed Instances – Requirements

1. Requires an Azure Virtual Network (VNet)
2. Requires a dedicated subnet and route table
3. Optionally, create an Azure VM (jumpbox) in the same subnet for direct access with SSMS

How do you migrate to Azure SQL Managed Instances?

Here are a few options:

1. Restore a native .bak file from Azure Blob Storage
2. Use Azure Database Migration Service (DMS) — mature and reliable
3. Use Azure Migrate: fully supported for VMware, Hyper-V, and on-premises servers

My questions

1. Noisy neighbors – we ran into weird performance problems in Amazon AWS. You buy an EC2 server and on your performance fluctuates a daily basis. But nothing has changed on our end. Why?

After I got AWS support cornered/escalated, they go – “Well it can be the neighbors on the same physical server.” Umhhh… yeah, not cool. I get that I am on a shared server, but I also get that I bought my CPU cores, RAM, etc. – so, no, I don’t really care about what my neighbors’ needs may be.

It’s just the same as if you rented an apartment, one day you come back and find a couple people inside, because the apartment complex decided to Airbnb your space while you went to get the mall. NOT COOL.

There will be multiple Managed Instances on the same physical server. So having a high fence around my server to prevent my resources being sucked out by other knuckleheads is important.

I would like to see more data on how Microsoft Azure will handle this noisy neighbors issue. They probably thought this through, but I need data.

2. What does storage/disk IO throughput look like? I need more information about Azure SQL Managed Instance performance.

3. Does it make sense to keep splitting DB into multiple filegroups, files to get more throughput? Does it make sense to have multiple DB files in blob storage and how does that affect performance?

4. Emailing out. How do we handle emailing out from the server? I like to get some reports/alerts, so yeah, I am going to need to email out.

5. Do I have any access into OS? I think the answer is No, but I would still like to ask. Can I at least see OS in read-only?

6. What other Azure resources are needed to launch this for small setup?

7. If I need to schedule PowerShell script to run, can I do that? And no, not SQL PS, but real full featured PowerShell…