Azure SQL DB - Scale Up - vCore Hyperscale or Azure Managed Instance?

Question

We are currently using Azure SQL DB for our data warehouse project. Though it is an OLAP application, it has partial OLTP functionality as well. Our DB's current configuration is Basic, DTU based. Now the requirement is that the DB size may increase till 10 TB. We need to scale up the DB. Which is the best suitable model? When analyzed, I found there are two options - vCore (Hyperscale) or Azure Managed Instance? Which one is the best?

Answer 1

Please take in consideration what features each option does not support:

• Geo-replication is not available on both.
• You can move your existing databases in Azure SQL Database to Hyperscale using the Azure portal. At this time, this is a one-way migration. You can't move databases from Hyperscale to another service tier, other than by exporting and importing data. For proofs of concept (POCs), it is recommened to make a copy of your production databases, and migrating the copy to Hyperscale.
• Hyperscale does not support automatic failover while Azure Managed instance does support it.
• You do not have point-in-time database restore with Hyperscale but you do have that with Azure Managed instances.
• Memory-optimized table variables have limited support on Hyperscale but have full support on Managed Instance. In-memory technologies have a great impact on performance for analytical and hybrid workloads. As explained here and here Managed Instance is better option.

Other considerations:

• If your database will be a extremely busy database and needs a really log write throughput, then Hyperscale is your choice.
• With Azure Managed Instance you can save money by using Azure Hybrid Benefit.
• Backups/restores and scale up/down are faster with Hyperscale.
• Columnstore indexes that are ideal for OLAP workloads are available to both, Hyperscale and Managed Instance.
• Both are primarily designed for transactional or OLTP workloads. However, they do support hybrid and OLAP workloads