By Kendra Little on December 8, 2023
Spoiler: a large amount of features from SQL Server 2022 are missing from Azure SQL Managed Instance. Some major features are missing that were introduced in SQL Server 2019– and here we are just a few weeks away from 2024.
But Microsoft’s top-line marketing claims about Azure SQL Managed Instance remain that “it’s always up to date with the latest SQL features and functionality.”
Let’s dig into some of the documented highlights on missing features, so you can decide for yourself what to think of that statement.
First, the marketing claim
On its main product page, the pitch for Azure SQL Managed Instance is:
Always operate on the latest version of SQL. Stop worrying about updates, upgrades, or end of support. SQL Managed Instance is built on the SQL Server engine, so it’s always up to date with the latest SQL features and functionality.
But I’m here to tell you, Managed Instance users DO need to worry about upgrades, because they are constantly confused about what Managed Instance can and can’t do at a given time compared to “normal” / boxed-product SQL Server. Some information is in documentation, but it’s scattered about in many places. Other limitations, well, users get to discover them on their own.
The missing hero: bidirectional DR to Azure SQL
Brent Ozar has written a great post describing how Azure SQL Managed Instance Link, a big hero of the SQL Server 2022 release, remains in a gated private preview that can only be accessed after filling out a form and waiting in a queue of indeterminate length.
Correction: The feature is now in Public Preview, ungated. Apologies for this error, more info on the public preview in this announcement from Nov 15, 2023.
As Brent writes:
It’s amazing to me that even a year after its release, SQL Server 2022’s flagship feature still isn’t ready yet.
Writable Query Store on Readable Secondaries is not available in Managed Instance
Brent points out in the post above that Writable Query Store on Readable Secondaries remains in preview for the boxed product.
This feature is not available at all in Azure SQL Managed Instance. I see no way to even preview it.
2019 Memory Optimized tempdb metadata is not available
I wrote a feedback article on the lack of the SQL Server 2019 feature memory optimized tempdb metadata in Managed Instance. Essentially, tempdb metadata contention occurs on system tables when you use tempdb at higher volumes. Unlike other types of tempdb contention, this can’t be alleviated by adding multiple data files, as these system tables only ever reside in one data file in tempdb. But if you hit this problem in Managed Instance, the feature that fixes it isn’t available to you.
tempdb file management is halfway there / livin on a prayer
Only recently, Microsoft made it possible to add data files to tempdb in Managed Instance.
However, you might notice that there are commands to add files and remove files, but NOT to set the file size or to grow files.
How do you make tempdb files the same size in Azure SQL Managed instance to follow Microsoft’s own performance guidelines for tempdb? I’d love to know.
SQL Server 2022 Intelligent Query Processing is hugely incomplete
None of the following intelligent query processing features are available in Managed Instance:
- Cardinality Estimation Feedback
- Memory Grant Feedback (percentile)
- Memory Grant, Cardinality Estimation, and DOP feedback persistence
- Optimized plan forcing
- Parameter Sensitivity Plan Optimization
Does that mean these features aren’t stable/ don’t work well/ aren’t reliable? Honestly, I have no idea. But I do know that if put my database compatibility level at 160, these might show up at any time without me understanding it, which essentially breaks the concept of database compatibility levels. (More on this in an upcoming post.)
You can’t have more than 100 databases per instance
Not all databases are of equal size, activity, or usage, so this cap seems weirdly arbitrary to me. But there it is.
You can’t get minimal logging (no simple or bulk recovery models)
Minimal logging is really nice for staging data and temporary workspaces. For many of these use cases, data loss of staged data is not a huge problem– you’d start the process over again anyway. However, you can’t do this anywhere but tempdb on SQL Server Managed Instance.
Remember those limitations for tempdb I mentioned above? Not fantastic.
You can’t lower the max server memory
Would you like to test how things perform if you scale down to have less memory? Well, the normal way to do this won’t work, time to get on your bad idea jeans.
You can’t take database snapshots or use DBCC CLONEDATABASE
DBCC CLONEDATABASE is really handy and useful. Sigh.
You can’t change the time zone after the instance is created
Hope you don’t want to.
You can’t use single user mode
This one I kind of understand, because they don’t want their processes to be locked out– but it still means that you need to create workarounds if you use this for maintenance.
You can’t run DBCC CHECKDB with options REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST, and REPAIR_REBUILD
Hopefully you don’t need to, but it’s good to understand this ahead of time.
You can’t use filestream, filetable, fulltext semantic search, merge replication, peer to peer replication, replication with updatable subscriptions
I thought a lot of these features were overhyped/not great anyway, but a lot of people disagree with me about that.
You can’t change the number of error logs, or even keep your error logs
This one makes me sad:
“Error logs that are available in SQL Managed Instance aren’t persisted, and their size isn’t included in the maximum storage limit. Error logs might be automatically erased if failover occurs. There might be gaps in the error log history because SQL Managed Instance was moved several times on several virtual machines.” Docs
You can’t use instant file initialization, so you may wait for file growths
Azure storage ain’t famous for being fast.
“A SQL Database managed instance does not use instant file initialization, so you might see additional PREEMPTIVE_OS_WRITEFILEGATHER wait statistics since the date files are filled with zero bytes during file growth.” docs
You can’t use some columnstore index features – maybe?
Hard for me to tell if this doc applies to managed instance or if it’s up to date.
You can’t use Log Shipping for anything other than migrating to MI
The only log shipping service available in Managed Instance is the Log Replay Service.
As far as I can tell from reading, that only works with Managed Instance as the destination. If this is incorrect, please correct me in the comments.
Isn’t it always a little creepy when it’s easier to get into something than it is to get out?
Also note:
LRS is the only method to restore differential backups on managed instances. It isn’t possible to manually restore differential backups on managed instances or to manually set the NORECOVERY mode by using T-SQL.
You can’t use event notifications
I actually liked that feature. Maybe I was the only one.
You can’t have reasonably sized data and log files in General Purpose
“In the General Purpose service tier, every database file gets dedicated IOPS and throughput that depend on the file size.” Docs
Having to create artifically large files gets real weird, real fast. And of course you get to pay for the storage.
I know there’s more, and I’ve probably missed something obvious
But you get the picture.
How do we fix this?
I don’t think it’s our responsibility as customers to fix this. I’ve been told by Microsoft folks that I should create “suggestions” for missing features in their forums, but… really?
Why should users need to file suggestions that a vendor follow the primary pitch for their own product? My time and your time is valuable, friends. We shouldn’t have to write suggestions and vote so that someone can pick and choose how they live up to their own commitments: they already committed to doing that.
If you are considering or using Managed Instance, perhaps let Microsoft Sales know that you are concerned about the number of missing features in Azure SQL Managed Instance, and that it’s important to you to have the latest features from SQL Server available. Not the imaginary ones, the actual ones.