How to choose the best Azure SQL service for your needs

In this post, I will compare the three main services that offer SQL capabilities in Azure: Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs.

In this post, I will compare three Azure SQL services: Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs. I will cover their main features, differences, billing options, and backup options. I will also explain the concepts of single database, elastic pools, and DTU model in Azure SQL Database.

But first, let me give you some examples of how you can use each of these services in your projects.

• Azure SQL Database is a fully managed relational database service that offers high availability, scalability, security, and intelligence. You can use it for applications that need a single database or multiple databases that can share resources. For example, you can use Azure SQL Database for a SaaS application that has a dedicated database for each customer or a shared database for each tenant group.
• Azure SQL Managed Instance is a fully managed instance of SQL Server that runs in a dedicated subnet in your virtual network. It offers near 100% compatibility with on-premises SQL Server, along with built-in high availability, backup, and security features. You can use it for applications that need a full SQL Server feature set or that require minimal changes when migrating from on-premises SQL Server. For example, you can use Azure SQL Managed Instance for a line-of-business application that uses features such as CLR, Service Broker, or cross-database queries.
• SQL Server on Azure VMs is an infrastructure as a service (IaaS) offering that lets you run SQL Server on an Azure virtual machine. It gives you full control over the installation, configuration, and maintenance of your SQL Server instance. You can use it for applications that need the most flexibility or customization of your SQL Server environment. For example, you can use SQL Server on Azure VMs for a legacy application that uses features such as Windows authentication, distributed transactions, or database mail.

Now that you have some idea of what each service can do, let's dive into the details of how they compare.

I will cover the following aspects of these services:

• Service models: PaaS vs IaaS
• Billing models: vCore vs DTU
• Compute tiers: Serverless vs Provisioned
• Service tiers: General Purpose, Business Critical, Hyperscale
• Single database vs Elastic pools
• Availability and backup options

Service models: PaaS vs IaaS

Azure SQL Database and Azure SQL Managed Instance are Platform as a Service (PaaS) offerings, which means that they provide a fully managed SQL environment in the cloud, without requiring the user to manage any infrastructure or operating system. They also offer features such as automatic scaling, patching, backups, high availability, and security.

SQL Server on Azure VMs is an Infrastructure as a Service (IaaS) offering, which means that it provides a virtual machine with SQL Server installed on it, but the user is responsible for managing the infrastructure, operating system, and SQL Server configuration. It offers more control and flexibility over the SQL environment, but also requires more maintenance and administration.

Billing models: vCore vs DTU

Azure SQL Database and Azure SQL Managed Instance offer two billing models: vCore and DTU.

vCore stands for virtual core, and it is a measure of the CPU and memory resources allocated to the SQL service. The user can choose the number of vCores and the amount of memory per vCore, depending on their workload and performance requirements. The user also pays for the storage size and type (standard or premium) separately.

DTU stands for Database Transaction Unit, and it is a measure of the combined CPU, memory, and I/O resources allocated to the SQL service. The user can choose from predefined DTU levels that include a fixed amount of storage. The user does not have to worry about the individual resource components, but only about the overall performance of the database. For example, a DTU level of 100 means that the database can handle up to 100 transactions per second. The DTU model is simpler and more predictable, but less flexible and granular than the vCore model.

Compute tiers: Serverless vs Provisioned

Azure SQL Database offers two compute tiers: Serverless and Provisioned.

Serverless is a compute tier that automatically scales the CPU and memory resources based on the workload demand, and pauses the database when it is not in use. The user pays only for the amount of resources consumed per second. Serverless is ideal for databases that have unpredictable or intermittent usage patterns, and that can tolerate some latency when resuming from the paused state.

Provisioned is a compute tier that allocates a fixed amount of CPU and memory resources to the database, and does not pause the database when it is not in use. The user pays for the reserved resources per hour, regardless of the actual usage. Provisioned is ideal for databases that have predictable or constant usage patterns, and that require low latency and high performance.

Azure SQL Managed Instance and SQL Server on Azure VMs only offer the Provisioned compute tier.

Service tiers: General Purpose, Business Critical, Hyperscale

Azure SQL Database and Azure SQL Managed Instance offer three service tiers: General Purpose, Business Critical, and Hyperscale.

General Purpose is a service tier that provides balanced and scalable performance for most business workloads. It uses remote storage (Azure Premium Storage) for the data files, which reduces the cost but also increases the I/O latency. It supports up to 80 vCores or 3000 DTUs, and up to 8 TB of storage.

Business Critical is a service tier that provides high performance and availability for mission-critical workloads. It uses local SSD storage for the data files, which increases the cost but also reduces the I/O latency. It supports up to 128 vCores or 4000 DTUs, and up to 4 TB of storage.

Hyperscale is a service tier that provides unlimited scalability and fast recovery for large-scale workloads. It uses a distributed architecture that separates the compute and storage layers, and allows for rapid backup and restore operations using file snapshots. It supports up to 128 vCores or 6000 DTUs, and up to 100 TB of storage.

SQL Server on Azure VMs does not have service tiers, but instead offers different sizes and types of virtual machines that vary in CPU, memory, storage, and network performance.

Single database vs Elastic pools

Azure SQL Database offers two deployment options: Single database and Elastic pools.

Single database is a deployment option that creates an isolated database with its own set of resources. The user can choose the compute tier, service tier, billing model, and resource allocation for each single database independently. Single database is ideal for databases that have different performance requirements or that need to be isolated from each other.

Elastic pools is a deployment option that creates a shared pool of resources for multiple databases. The user can choose the compute tier, service tier, billing model, and resource allocation for the entire pool, and then assign databases to the pool. The databases in the pool can share the resources dynamically, depending on their demand. Elastic pools are ideal for databases that have similar performance requirements or that need to be consolidated to save costs.

Azure SQL Managed Instance and SQL Server on Azure VMs only support multiple databases within a single instance or virtual machine.

Availability and backup options

Azure SQL Database and Azure SQL Managed Instance provide built-in high availability and backup features that ensure data durability and reliability.

High availability is achieved by creating multiple replicas of the database or instance across different fault domains within an Azure region. In case of a failure, one of the replicas automatically takes over as the primary without any data loss or downtime. The user can also enable geo-replication to create secondary replicas in different Azure regions for disaster recovery purposes.

Backup is achieved by creating full, differential, and transaction log backups of the database or instance periodically. The backups are stored in Azure Blob Storage with read-access geo-redundant storage (RA-GRS) enabled. The user can restore the database or instance to any point in time within the backup retention period (7 to 35 days).

SQL Server on Azure VMs does not provide built-in high availability or backup features, but instead relies on the user to configure them using SQL Server tools and features such as Always On Availability Groups, Failover Cluster Instances, Backup and Restore, etc.

Quick Feature Comparison

The following table summarizes some of the key differences between the three services:

The following table compares some of the billing options for the three services:

Summary

In this post, I have compared three Azure SQL services: Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs. I have covered their main features, differences, billing options, and backup options. I have also explained the concepts of single database, elastic pools, and DTU model in Azure SQL Database.

I hope you found this post useful and informative. If you have any questions or feedback, please leave a comment below or contact me directly.

References:

1. What is the Azure SQL Database service? - Azure SQL Database | Microsoft Learn
2. What is Azure SQL Managed Instance? - Azure SQL Managed Instance | Microsoft Learn
3. What is Azure SQL? - Azure SQL | Microsoft Learn
4. What is PaaS? Platform as a Service | Microsoft Azure
5. What is IaaS? Infrastructure as a Service | Microsoft Azure
6. vCore purchasing model - Azure SQL Database | Microsoft Learn
7. DTU-based purchasing model - Azure SQL Database | Microsoft Learn
8. Serverless compute tier - Azure SQL Database | Microsoft Learn
9. Pricing - Azure SQL Database Single Database | Microsoft Azure
10. What is a single database? - Azure SQL Database | Microsoft Learn
11. Manage elastic pools - Azure SQL Database | Microsoft Learn