Azure SQL
/What is Azure SQL?
Azure SQL is the term used to refer to the family of SQL database services, hosted on Azure, underpinned by the Microsoft SQL Server database engine. Microsoft provides several Azure SQL deployment options that provide varying levels of control and flexibility to meet different customer needs.
Deployment Options
There are a number of factors that need to be taken into consideration when deciding on which Azure SQL deployment option is best. Some of these include Total Cost of Ownership (TCO), IaaS vs. PaaS, balance between control and responsibility. From an Azure services perspective, there are ultimately three high-level options:
Ideal for lift & shift migrations which require operating system level access.
100% SQL Server compatibility.
Ideal for most migrations which require access to instance scoped features (e.g. SQL Server Agent, Service Broker, Common Language Runtime (CLR), etc).
Near 100% feature parity with the SQL Server database engine.
Ideal for cloud-based applications.
Modern capabilities such as Serverless compute and Hyperscale storage.
Choose the right Azure database for your needs
| Service | Type | Resource model | Best for |
|---|---|---|---|
| SQL Server on Azure Virtual Machines | IaaS | Virtual Machine | Best for migrations requiring OS-level access and control. |
| Azure SQL Managed Instance | PaaS | Instance | Best for most lift and shift migrations. |
| Azure SQL Database | PaaS | Database | Best for modern cloud applications. |
Azure SQL - Responsibility Model
| ✔ | Microsoft-Managed | ✘ | Self-Managed |
| Responsibility | SQL Server | SQL Server on Azure | Azure SQL MI | Azure SQL DB |
|---|---|---|---|---|
| On-Premises | IaaS | PaaS | ||
| Hardware | ✘ | ✔ | ✔ | ✔ |
| Operating System | ✘ | ✘ | ✔ | ✔ |
| SQL Server | ✘ | ✘ | ✘ | ✔ |
Azure SQL PaaS - Deployment Options Matrix
| Azure SQL Database (vCore) | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Model | vCore | |||||||||
| Type | Single Database | Elastic Pool | ||||||||
| Service | General Purpose | Hyperscale | Business Critical | General Purpose | Business Critical | |||||
| Hardware | Gen5 | Fsv2 | Gen5 | Gen5 | M | Gen5 | Fsv2 | Gen5 | M | |
| Compute | Serverless | Provisioned | ||||||||
| vCores | 1 to 40 | 2 to 80 | 4 to 80 | |||||||
| Data Max | 1GB to 4TB | <= 100TB | 1GB to 4TB | |||||||
| Azure SQL Database (DTU) | ||||||
|---|---|---|---|---|---|---|
| Model | DTU | |||||
| Type | Single Database | Elastic Pool | ||||
| Service | Basic | Standard | Premium | Basic | Standard | Premium |
| Hardware | Not Applicable | |||||
| Compute | Provisioned | |||||
| DTUs | 5 | 10 to 3K | 125 to 4K | 40 to 1.6K | 50 to 4K | 125 to 4K |
| Data Max | <= 2GB | <= 1TB | <= 4TB | 5 to 156GB | 50GB to 4TB | |
| Azure SQL Managed Instance | |||
|---|---|---|---|
| Model | vCore | ||
| Type | Single Instance | Instance Pool | |
| Service | General Purpose | Business Critical | General Purpose |
| Hardware | Gen5 | ||
| Compute | Provisioned | ||
| vCores | 4 to 80 | 8 to 80 | |
| Data Max | 32GB to 8TB | 32GB to 4TB | <= 8TB |
Purchase Model
There are two purchasing models to consider when thinking about provisioning an Azure SQL PaaS resource - vCore and DTU. Why multiple options? It ultimately boils down to choice, different customers have different SQL needs. See below for a breakdown of some of the key differences between the two purchasing models that may influence your decision. For more detailed information, see Microsoft Docs: Purchasing Models and YouTube: Azure SQL Capacity Planning.
vCore (virtual core)
A vCore represents a logical CPU with the ability to choose between different hardware options (e.g. Gen5, Fsv2, M, etc).
Resources are able to be scaled independently (i.e. storage, compute, and I/O).
Ability to use Azure Hybrid Benefit and/or Reserved Capacity for potential cost savings.
Simplest purchasing model for customers that have existing SQL Server workloads.
Easier to right-size the destination environment by removing the analysis required with the DTU purchasing model.
DTU (Database Transaction Unit)
A DTU represents a blended measure of CPU, memory, reads, and writes.
Simple, pre-configured bundles of compute, storage, and I/O (i.e. storage and compute are scaled linearly).
Best for customers who want simple, pre-configured resource options.
Use the DTU calculator to estimate the number of DTUs required for existing SQL Server workloads.
Note:
Some deployment configurations are limited to a specific purchasing model (e.g. Hyperscale is only available under the vCore model), please refer to the deployment options summary table above for an at-a-glance overview of the various configuration options.
Microsoft remains committed to the DTU-based purchasing model and the simplicity it offers customers who want a pre-configured solution.
Resource Type
When provisioning an Azure SQL Database or Azure SQL Managed Instance, there are different resource types available within each service (e.g. single database/instance vs. elastic/instance pool), each with their own unique set of characteristics.
Azure SQL Database
Single database with its own set of resources.
Managed via a logical SQL Server.
Modern capabilities such as Hyperscale storage and Serverless compute.
Manage multiple databases that share a set number of resources at a set price.
Managed via a single server.
Ideal for SaaS developers with multi-tenanted solutions.
Azure SQL Managed Instance
Near 100% compatibility with the latest SQL Server (Enterprise Edition) database engine.
Native virtual network (VNet) support.
PaaS capabilities (automatic patching/version updates/backups, high availability).
Instance Pool (Public Preview)
Pre-provision compute resources according to total migration requirements.
Migrate smaller SQL Server instances (e.g. ability to host 2-vCore instances).
Predictable and fast deployment time.
Service Tier
Azure SQL Database and Azure SQL Managed Instance are based on the SQL Server database engine architecture, adjusted for the cloud to provide 99.99% availability through planned events (e.g. operating system or database engine upgrades), as well as unplanned events (e.g. hardware/software/network failures). Note: Higher availability configurations are available (e.g. 99.995% with zone redundant databases in the Business Critical tier).
The default service tier is designed to cater for the most generic workloads. If you need a fully managed database engine with 99.99% availability and storage latency between 5 and 10 ms, the General Purpose tier is the option for you.
Architecture model is based on a separation of compute and storage (two layers).
Stateless compute layer running the sqlservr.exe process which contains only transient and cached data (In-Memory: Plan Cache, Buffer Pool, Columnstore Pool; Local SSD: TempDB, Model Databases).
Stateful data layer with database files (.mdf/.ldf) stored in Azure Blob storage.
In the event of a failure or planned system maintenance, Azure Service Fabric will move the stateless process to a spare node with sufficient capacity. This process guarantees 99.99% availability.
Azure Blob storage transparently replicates database files and guarantees no data loss if underlying infrastructure failures occur.
Designed for applications that require low-latency from the collocated SSD storage (1 to 2 ms), fast recovery if the underlying infrastructure fails, or need to off-load read-only workloads to the free of charge readable secondary replica.
Each database in the Business Critical/Premium service tier is automatically provisioned with a primary read-write replica and several read-only replicas.
Architecture model relies on a fact that there is always a quorum of available database engine nodes.
Designed for intensive workloads that cannot tolerate any performance impact due to the ongoing maintenance operations.
Compute and storage is collocated on each node providing low latency.
High availability is achieved by replication to establish a four node cluster using an Always On availability group.
The primary node is constantly pushing changes to the secondary nodes.
Business Critical has built-in Read Scale-Out capability that provides the ability to offload read-only workloads using the compute capacity of one of the read-only replicas.
Intended for most business workloads as it provides independently scalable compute and storage with the ability to autoscale up to 100 TB.
Nearly instantaneous database backups regardless of size with no IO impact on compute resources (implemented using storage snapshots). Fast database restores (minutes rather than hours or days).
Ability to provision one or more read-only replicas for offloading read workloads (read scale-out) as well as acting as hot standbys in the event of a failover.
Availability model consists of four layers:
Stateless compute layer running the sqlservr.exe process which contains only transient and cached data (In-Memory: Plan Cache, Buffer Pool, Columnstore Pool; Local SSD: RBPEX cache, TempDB, Model Databases).
Stateless storage layer which consists of page servers. This layer is the distributed storage engine for the sqlservr.exe processes running on the compute replicas. Each page server contains only transient and cached data (In-Memory: Cached Data Pages; Local SSD: RBPEX). Each page server has a paired page server in an active-active configuration to provide load balancing, redundancy, and high availability.
Stateful transaction log storage layer formed by the compute node running the log service process, the transaction log landing zone, and transaction log long term storage.
Stateful data storage layer with database files (.mdf/.ndf) stored in Azure Blob storage. These data files are kept updated by the page servers.
Compute Tier
One of the modern features of Microsoft’s fully managed Azure SQL Database service is the ability to opt for “serverless” compute as opposed to the traditional provisioned model. This is ideal for bursty/unpredictable workloads which can result in cost savings by only paying for the compute resources actually consumed on a per-second basis.
How do I know if serverless is a good fit for my workload? Generally speaking, in the case of a single database, if the average compute utilization is less than 50% (memory and cpu consumption), serverless will likely be a better fit from a pricing perspective.
Provisioned
Fixed resource allocation regardless of usage.
Unit price (cost per vCore per unit time) is lower than serverless compute tier.
Pay for provisioned resource regardless of consumption, billed on an hourly basis.
Ideal for workloads with regular and substantial compute utilization.
CPU and memory scale in lock step when resizing (memory-to-vcore ratio fixed at ~5GB to 1 vCore).
Scale-up time is at least order of a few minutes, resets memory, and always drops connections.
Automatically scales compute based on workload demand. Automatically pauses and resumes (e.g. inactive period).
Unit price (cost per vCore per unit time) is higher than provisioned compute tier.
Cost-effective, pay only for compute resources you consume on a per-second basis.
Ideal for workloads with unpredictable and intermittent usage patterns.
CPU and memory scale independently to align with workload demand (memory-to-vcore ratio adapts based on usage, ranges up to 24GB to 1 vCore).
Scale-up time is typically sub-second, occasionally a few minutes if load balancing is required.
Minimum and maximum vCores are configurable parameters that define the range of compute capacity available for the database. Memory and IO limits are proportional the the vCore range specified.
Azure SQL Database Serverless Resource Allocation and Usage
Azure SQL Database Serverless 3 Tier Architecture
Hardware
When it comes to Azure SQL Database, the vCore purchasing model provides customers the option to choose between several hardware generation options that are optimized for different workloads.
Balanced compute and memory resources.
Suitable for most database workloads that do not have higher memory, higher vCore, or faster single vCore requirements as provided bv FSv2 or M-series.
Compute optimized hardware for workloads demanding more CPU, delivering low CPU latency and high clock speeds.
FSv2 can improve query response time and workload throughput compared to Gen5.
Memory optimized hardware for workloads demanding more memory and higher compute limits compared to Gen5.
M-series provides 29GB per vCore and 128 vCores which increases the memory limit in SQL Database to nearly 4TB.
Resources
Product Pages
Video
Microsoft Learn