By Kendra Little on January 22, 2024
Whether or not you use Azure SQL Managed Instance, you will likely be asked for an opinion on it eventually if you’re a SQL Server person.
While the architecture is documented, it can be a bit of a long read and some of the gotchas are spread out over different pages – so I’m drawing up the architecture of each service tier along with notable implications for the design on performance and cost. Here’s the scoop on General Purpose.
Architecture for Managed Instance in General Purpose
Here’s the basic architecture for the General Purpose Service Tier. It’s basically a failover cluster style design with one active virtual compute node and shared storage.
Gotchas: storage
The big old gotcha is that the shared storage is Azure Blob Storage, which is not renowned for its speed. There is also a maximum number of IOPs based on data file size which maxes out at a throughput of 250 MiB/s– your IO will be throttled if it exceeds this.
Because of the pokey storage, the docs for General Purpose notes regarding failovers that:
a heavy workload might experience some performance degradation during the transition since the new database engine process starts with cold cache.
No kiddin'.
There’s an instance limit on maximum log write throughput, too. For transaction log writes on General Purpose you will be cruising at or below 4.5 MiB/s per vCore, Max 120 MiB/s per instance, and 22 - 65 MiB/s per DB (depending on log file size).
What about memory?
While you can provision memory to compensate/lower physical reads for data files, there’s no individual “memory” slider– the amount of memory you have is proportional to your number of vCores:
- 5.1GB RAM per vCore under Standard Series hardware: 8 vCore = only 40.8GB RAM
- 7GB RAM per vCore under Premium Series: 8 vCore = only 56GB RAM
- 13.6GB RAM per vCore under Premium Memory Optimized: 8 vCore= only 108.8 GB RAM
As someone who had multiple clients with 2TB of RAM on failover clusters 8 years ago on on-prem hardware, these numbers still take some getting used to. Busy SQL Servers eat memory like tourists at a Vegas luxury buffet.
In effect, you’ll often need to overspend on CPU if you want to use buffer pool to reduce physical IO for larger databases – make sure to factor that into cost estimates. And you’ll probably also end up having larger data files and log files than you might need, as well, in order to get more IOPs. (Yep, you pay for storage over 32GB, based on how big those files are, too.)
Pricing examples: General Purpose and Business Critical
With all these tradeoffs, you might think, “Why not just use Business Critical for databases with high performance requirements?” Well, Business Critical has its own gotchas that I’ll dig into in a future post, but arguably the biggest differentiator of the Business Critical Tier is price.
Using the East-US region as an example and assuming the instance needs to be on all the time for one year (8,760 hours):
- 16 vCore General Purpose x Standard Series = $4.035/hour pay-as-you-go = $35,346.60/year
- 16 vCore General Purpose x Premium Series Memory Optimized = $5.541/hour pay-as-you-go = $48,539.16/year
- 16 vCore Business Critical x Standard Series = $10.871 pay-as-you-go = $95,229.96 /year
- 16 vCore Business Critical x Premium Series Memory Optimized = $13.883/hour pay-as-you-go = $121,615.08/year
For simplicity I only listed Standard Series and Memory Optimized at 16vCores– use the pricing calculator if you want to see other options. These pricing examples don’t include Failover Groups— multiply by 2 to estimate that.
Yep, you can pay $121K USD per year for — let’s see— only 217.6 GB of RAM if you’re at 16 vCores in the Business Critical Service Tier with Premium Series Memory Optimized. (Blinks)
You can lower pricing using reserved capacity, but make sure you read limitations on Self-service exchanges and refunds for Azure Reservations before committing. There are limitations on the refund policy like, “Azure doesn’t process any refund that exceeds the 50,000 USD limit in a 12-month window for a billing profile or EA enrollment.” So be very, very sure before you commit to reservations.
Will Managed Instance pricing go down in some way?
I do think Managed Instance is a great idea: I don’t want to administer failover clusters or Availability Groups myself– and it’s rare that I meet anyone who loves doing that. Managed Services are very attractive to alleviate that pain point. And I do think that automated administration and management of these services is worth paying for.
However, this is a VERY expensive service. I hope the value per dollar goes up in the future, both in terms of keeping Managed Instance up to date with features and in giving you more memory and faster storage for your dollar. In my (completely anecdotal personal opinion), price is one of the bigger blockers for adoption, as Aaron Bertrand explains regarding Stack Overflow’s Azure Migration.
Read more
Check out more posts in this series: Is the Azure SQL Managed Instance Business Critical Service Tier Worth the Cost?