Getting started
When it comes to SQL databases, there are a lot of different options in Microsoft Azure which range from Serverless to Elastic, or just from a managed instance (SQL Server) to Synaps Analytics SQL Pools. Depending on the requirements of your migration,Cloud-Native approach or development, one option will be a better fit than the other and so the size of your database will also become important, especially in enterprise environments.
This can sometimes lead to harder choices when it comes to the perfect fit for your data workloads and it can turn into a tradeoff between size and functionality. For this blog, let's dive in the sizing part and leave the comparison in functionality for another time.
Sizing of SQL Services
There are a few Azure SQL Services that are commonly used in Small and Medium Business (SMB) and Enterprise environments. I will focus on the most common ones, which are the Azure SQL Database, Azure SQL Managed Instance, Azure Cosmos DB (SQL core) and Azure Synaps Analytics (SQL Pool). Each of these four services has different tiers, allowing for different storage limits. I will be looking at the highest possible option for each service.
Azure SQL Database
Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement. Azure SQL Database is always running on the latest stable version of the SQL Server database engine and patched OS with 99.99% availability. PaaS capabilities that are built into Azure SQL Database enable you to focus on the domain-specific database administration and optimization activities that are critical for your business.
Source: Microsoft
An Azure SQL Database comes close to an SQL Server Database you might be used to from on-premise environments, but with some adjusted functionality. Azure SQL Databases comes in 2 variaties, vCore-based (separate CPU, Memory and I/O) and DTUs (Combined set of CPU, Memory and I/O). Each model has its own set of features, but both have a maximum of 4TB of storage.
There is an exception within the vCore-based model, which is Hyperscale. Hyperscale allows for rapid scaling of your database and has fully separated CPU, Memory, I/O and Storage. This means that the CPU power could come from a different rack in Microsoft's datacenter than where the memory comes from, while still granting the same performance you'd get if all the components were placed in the same physical server. A Hyperscale database allows for 100TB of storage, but with some trade offs on functionality.
NOTE: For the DTU-based model it is hard to say what storage would cost per 1GB or more, since it is bakked within each Tier. For the vCore-based model however 1GB of storage would cost €0,10/ $0,13 for a Single database, when it is an Elastic Pool it will be €0,23/ $0.25 per 1GB.
Azure SQL Managed Instance
Azure SQL Managed Instance is the intelligent, scalable cloud database service that combines the broadest SQL Server database engine compatibility with all the benefits of a fully managed and evergreen platform as a service. SQL Managed Instance has near 100% compatibility with the latest SQL Server (Enterprise Edition) database engine, providing a native virtual network (VNet) implementation that addresses common security concerns, and a business model favorable for existing SQL Server customers. SQL Managed Instance allows existing SQL Server customers to lift and shift their on-premises applications to the cloud with minimal application and database changes. At the same time, SQL Managed Instance preserves all PaaS capabilities (automatic patching and version updates, automated backups, high availability) that drastically reduce management overhead and TCO.
Source: Microsoft
The Azure SQL Managed Instance is similar in every way to the On-premise SQL Server and is often used for Lift and Shift migration from On-premise to the Cloud. Azure SQL Managed Instances also has 2 variations, called General Purpose and Business Critical, and both variations come with their own features.
An Azure SQL Managed Instance does have 3 tiers available, which are influenced by the General Purpose or Business Critical option.
As you can see on the above image, the maximum on an Azure SQL Managed Instance is 16TB.
NOTE: Storage for an Azure SQL Managed Instance comes in steps of 32GB at a time. Each step of 32GB on General Purpose will cost you about €3,31/ $3.68. If you are on Business Critical it will cost €7,20 / $8 per step.
Cosmos DB (SQL core)
Azure Cosmos DB is a fully managed NoSQL database for modern app development. Single-digit millisecond response times, and automatic and instant scalability, guarantee speed at any scale. Business continuity is assured with SLA-backed availability and enterprise-grade security. App development is faster and more productive thanks to turnkey multi region data distribution anywhere in the world, open source APIs and SDKs for popular languages. As a fully managed service, Azure Cosmos DB takes database administration off your hands with automatic management, updates and patching. It also handles capacity management with cost-effective serverless and automatic scaling options that respond to application needs to match capacity with demand.
Source: Microsoft
While Cosmos DB is amazing for modern web development due to all its features and the 99.99% uptime SLA guarantee, it also comes in 2 variations which influence the possible amount of storage.
Serverless
Azure Cosmos DB Serverless uses a pay-per-use model, only charging for the Request units (RUs) and the storage being used. But Serverless also has a limit of 50GB per container with a maximum of 100 containers.
Provisioned Throughput
Azure Cosmos DB Provisioned Throughput has two different models, the first of which allows for a predefined set of RUs and storage and is called Standard. The other model is called Autoscale, which allows for automatic up-and-down scaling of RUs and storage required for the current workloads. Provisioned Throughput has an unlimited storage capacity for its databases/ containers.
NOTE: Every 1GB of storage comes a long with 10 RUs of compute. Each 1GB costs you about €0,23/ $0.25 and for every 10 RUs about €0,50 / $0.60.
Azure Synaps Analytics (SQL Pool)
Azure Synapse Analytics is an analytics service that brings together enterprise data warehousing and Big Data analytics. Dedicated SQL pool (formerly SQL DW) refers to the enterprise data warehousing features that are available in Azure Synapse Analytics.
Dedicated SQL pool represents a collection of analytic resources that are provisioned when using Synapse SQL. The size of a dedicated SQL pool is determined by Data Warehousing Units (DWU).
Once your dedicated SQL pool is created, you can import big data with simple PolyBase T-SQL queries, and then use the power of the distributed query engine to run high-performance analytics. As you integrate and analyze the data, dedicated SQL pool will become the single version of truth your business can count on for faster and more robust insights.
Source: Microsoft
Within a Dedicated SQL pool, there are also 2 variations when it comes to databases. These are not really variations of the databases itself, but they rather come down to variations in table types within the database which contribute to maximum storage capacity.
A Database within a Dedicated SQL Pool knows 2 types of tables, Columnstore-tables and Rowstore-tables. Columnstore-tables are unlimited when it comes to storage, while rowstore-tables have a maximum of 240TB compressed.
NOTE: You pay about €21/ $23 per 1TB of storage on a Dedicated SQL Pool.
Conclusion
While the above four SQL services are meant for different types of workloads, they are sometimes used interchangeably. An example would be that an Azure SQL Database is being used as Data Warehouse or a Cosmos DB solely for handling transactions. They do all have something in common, which comes down to the following: the more storage you need, the larger the cost will be with all the extra compute that comes with it. Unlimited for Cosmos DB and Synaps Analytics sound great but you will pay dearly for it.
Plan carefully which service will suit your database needs, which features and functionality are required and what your current storage as well as what your expected storage growth will be for the coming years.
If rework is needed to fit your database on a Hyperscale Azure SQL Database instead of an Azure SQL Managed Instance, for example, it might just be worth it when you look at the numbers in the end.
What's next?
There is a small delay on the audio project I'm working on, but it will be released soon! Today is also the start of my holiday, I'll be back next week with something interesting about purposely breaking your services. Stay tuned!