Case Study: Choosing The Right Offering, Model and Tier for Azure SQL Database

Requirement: A relational data store needs to be provisioned on Azure specifically on SQL Server platform.

Problem Statement: There are multiple offerings by Microsoft in SQL Server space like Azure SQL Server which is a DBaaS (Database as a Service) and further differentiated by DTU based model, vCore based model and again different tiers for different types of workloads like OLTP (General-Purpose Tier) and OLAP (Business Critical Tier) applications, Azure SQL Managed Instance, SQL on Azure VM, Azure Synapse Analytics etc. So, the challenge is to identify the right offering with right level of services in it.

Scope: We are going to study/analyze Azure SQL Database, Azure SQL Managed Instance and Azure Synapse Analytics against our needs.

Business Objective: While there is no compromise on the functional requirements but the primary objective of is to select the cost effective solution with more focus on the storage so that it fulfils the current amount of space needed and capable to scale up/out within an economical way for future needs.

Actual Study: Considering lot of diversity in different offerings across tiers in their capacities, specifications and the variation in price calculation, we first captured all those details so that we could compare the actual cost against our requirements. This is what the table below specifies –

Before we proceed further, let’s quickly enumerate the key takeaways from this exercise –

1. There is a general perception or a thought in the mind that Azure SQL Managed Instance and vCore based offering over Azure SQL Database costs similar but you can see there is a significant cost difference per vCore price over two platforms
2. Managed Instance by default includes 32 GB of space in its price as per vCores. If you choose size > 32 gigs, equivalent price is adjusted as discount in the cost estimates
3. Similar storage is included in DTU based model too. About 250 gigs in the Standard tier and 500 gigs in the Premium tier
4. A huge difference in per vCore cost in DTU based Standard and Premium tier. Premium tier costs almost near to three fold of Standard tier
5. DTU based Standard tier supports a database size upto 1TB maximum
6. DTU based Premium tier can have a database growing upto 4TB maximum
7. It means, an Azure SQL Database can be upto 4TB only. However, there is another tier called Hyperscale which allows the database size upto 100TB so we will consider this option too during our case study
8. Azure Synapse Analytics charge for the storage per TB
9. Hyperscale tier charges you for the allocated storage only, not on the provisioned size

Primarily, we have been looking for the service which can support upto 8TB or more in terms of storage though we could start from less so we tried to capture our further details based on 2TB, 4TB or 8TB storage allocations. Idea here is to start small and then scale up.

We started our study with Azure Managed Instance as it was a preferable choice too but not concluded yet. So, let’s see how does it stand out in terms of our storage need and at that level how much compute does it offer –

This indicates that we can start small and then can have 8TB of storage within the reach of 8 vCores only and by considering all the calculations, it would cost close to 18K per month. Not mentioning the currency again as it doesn’t matter much to understand the concept though in the verdict yes, it would have the weightage.

We are not considering the hybrid benefits that you can avail for the lower environments (non-production workloads) or if you have your own license.

Our next target was Azure SQL Database and it seriously revealed several interested points that we never had on top of our mind –

vCore based model clearly shows that if you need close to 2 TB of space (in fact, portal shows it as 1.5TB), you would require to choose at least 10 vCores costing around 2K (almost 3 fold of Managed Instance). Maximum storage allowed by this tier (4TB) is offered with a minimum of 24 vCores costing about 4.5K which is again more than three times than Managed Instance. No doubt, you get more power with more CPUs but if you don’t need much power and storage is your primary need then Managed Instance clearly comes out a winner against vCore based model.

Next, we evaluated DTU based model to determine if that outweighs our preference of Managed Instance. Let’s see.

It gives an excellent option to start small with just 1 TB of size within 100 DTUs limit costing even under 300 euros. Perfect! Wait! 1 TB is the maximum limit of Standard tier under this model and to get our database beyond this limit, we need to switch to Premium tier. If you remember from the first table, there is a significant difference in per DTU cost between Standard vs. Premium tier (1.26 vs 3.37). Under the Premium Tier, 2 TB of storage is given with a minimum of 1750 DTUs costing around 6K euros though it stands flexible in this tier and you can scale it upto 4 TB which is the maximum limit in this tier i.e. you are not charged for the storage separately but paying the cost for 1750 DTUs makes all the difference.

And again, if storage is your need and you don’t need too much power then DTU based option also doesn’t stand out good for the verdict. With this, Managed Instance continues to be first runner until now after ruling out Azure SQL Database offerings under vCore based on DTU based models.

Let’s now review another tier, Hyperscale tier, which provides the maximum storage for a database upto 100TB. Basically, this is also a core-based offering and here are you are charged separately for the primary database and a read secondary replica. We won’t be considering the secondary replicas for our study as this is not the primary requirement for us though a difference in the cost is mentioned in the first table in this post at the top.

Hyperscale tier starts offering 1TB space with a minimum of 4 cores and 4TB/8TB storage with 8 cores itself. Price wise this is marginally higher than Managed Instance (1818 vs. 2229 euros for 8TB storage), so comes out a good competitor to it. Let’s park it for now.

At last, let’s take a look at the pricing for Azure Synapse Analytics too. Similar to DTU model, it has cDWUs (Data Warehouse Units). Cost per DWU varies depending on the no. of cDWUs selected.

Azure Synapse is based on MPP architecture and give the flexibility to pause/start the service. So, it is estimated for two scenarios here – one full month (730 hours) and 22 working days (528 hours). Here storage is not linked to cDWUs selected and is fully isolated. You can make your own combination e.g. if we need 8 TB of storage and want to go with 400 cDWUs for 22 working days, it would cost somewhere 3.6K euros which seems like double of Managed Instance. However, if offers unlimited storage flexibility and MPP architecture for distributed processing but with some cost. So, again, as not looking for high compute powers and with no need of big volume of data so Managed Instance still seems to be a better option than Azure Synapse for our workload. But, if the budgets permits then this can be an awesome option to go for.

After considering all the options, the final call remains between Managed Instance and Hyperscale tier of Azure SQL Database.

Considering the current limitations of Hyperscale like you can’t switch back to another tier once you are in Hyperscale and with not much feedback is causing less comfort level which can be increased with some proof of concept but for a quick decision and considering the aim to consider the most economical service meeting our requirements, Managed Instance seems to win the verdict with more of comfort too. However, during this entire exercise, we found that Azure Synapse can also be a good option if budgets are not tight and some R&D towards Hyperscale tier can also boost the confidence to try them as well.

Primary parameters for the comparison here have been the lookout for about 8TB of storage, minimum cost with maximum benefits. However, if the primary requirement is compute or a superior balance between compute and cost with little open budgets, the verdict definitely would vary and once again in that Azure Synapse might be the first and best choice.

Hope you found this case study interesting and informative too though there might be different opinions, preferences and the concluding remarks.