Azure SQL Database is Microsoft’s database-as-a-service offering that provides a tremendous amount of flexibility. It is built as part of the platform-as-a-service environment which provides customers with additional monitoring and security for the product.
Microsoft is continually working on improving their products and Azure SQL Database is no different. Many of the newer features we have in SQL Server were initially launched in Azure SQL Database, including (but not limited to) Always Encrypted, Dynamic Data Masking, Row Level Security, and Query Store.
DTU Pricing Tier
When Azure SQL Database first launched, there was a single pricing option known as “DTUs” or Database Transaction Units. (Andy Mallon, @AMtwo, explains DTUs in "What the heck is a DTU?") The DTU model provides three tiers of service, basic, standard, and premium. The basic tier provides up to 5 DTUs with standard storage. The standard tier supports from 10 up to 3000 DTUs with standard storage and the premium tier supports 125 up to 4000 DTUs with premium storage, which is orders of magnitude faster than standard storage.
vCore Pricing Tier
Fast forward a few years after Azure SQL Database was released to when Azure SQL Managed Instance was in public preview, and “vCores” (virtual cores) were announced for Azure SQL Database. These introduced the general-purpose and business-critical tiers with Gen 4 and Gen 5 processors. Gen 5 is the primary hardware option now for most regions since Gen 4 is aging out.
Gen 5 supports as few as 2 vCores and up to 80 vCores with ram being allocated at 5.1 GB per vCore. The general-purpose tier provides remote storage with max data IOPS ranging from 640 for a 2 vCore database up to 25,600 for an 80 vCore database. The business-critical tier has local SSD which provide much better IO performance with max data IOPS ranging from 8000 for a 2 vCore database up to 204,800 for an 80 vCore database. Both general-purpose and business-critical tiers max out at 4,096GB for storage, and this became a limitation for many customers.
HyperScale Database
To solve for the 4TB limit of Azure SQL Database, Microsoft created the hyperscale tier. Hyperscale allows customers to scale up to 100TB of database size in addition to providing rapid scale out for read-only nodes. You can also easily scale up and down within the vCore model. Hyperscale databases are provisioned using vCores. With Gen 5, a Hyperscale database can use between 2 – 80 vCores and 500 – 204,800 IOPS. Hyperscale achieves high performance from each compute node having SSD-based caches which helps minimize the network round trips to fetch data. There is a lot of awesome technology involved with Hyperscale in how it is architected to use SSD-based caches and page servers. I highly recommend that you take a look at the diagram that breaks down the architecture and how it all works in this article.
Serverless Database
Another request that was very common from customers was the ability to automatically scale up and scale down their Azure SQL Database as workloads increase and decrease. Customers have traditionally had the ability to programmatically scale up and down using PowerShell, Azure Automation, and other methods. Microsoft took that idea and built a new compute tier called Azure SQL Database serverless, which became generally available in November 2019. They allow the customer to set minimum and maximum numbers of vCores. This way they can know that there is always a minimum compute level available, and they can always automatically scale to a designated compute level. There is also the ability to configure an autopause delay. This setting allows you to automatically pause the database after a specific amount of time that the database has been inactive. When a database enters the autopause stage, compute costs goes to zero and only storage costs are incurred. The overall cost of serverless is the summation of the compute cost and storage cost. When the compute usage is between the minimum and maximum limits, compute cost is based on vCores and memory used. If actual usage is below the minimum value, compute cost is based on the minimum vCores and minimum memory configured.
The serverless tier has the potential to save customers a great deal of money while also giving them the ability to provide a consistent database user experience with the database being able to scale up as demand requires it.
Elastic Pools
Azure SQL Database has a shared resource model that enables customers to have a higher resource utilization. A customer can create an elastic pool and move databases into that pool. Each database can then start sharing predefined resources within that pool. Elastic pools can be configured using the DTU pricing model, or the vCore model. Customers determine the amount of resources that the elastic pool needs to handle the workload for all its databases. Resource limits can be configured per database so that one database can’t consume the entire pool. Elastic pools are great for customers who have to manage a large number of databases or multitenant scenarios.
New Hardware Configuration for Provisioned Compute Tier
The Gen4/Gen5 hardware configurations are considered "balanced memory and compute." This works well for many SQL Server workloads, however, there have been use cases for a lower CPU latency and higher clock speed for CPU-heavy workloads and a need for higher memory per vCore. Microsoft has once again delivered and created a compute optimized and memory optimized hardware configuration. These are currently in preview and only available in certain regions.
In the general-purpose provisioned tier you can select the Fsv2 Series which can deliver more CPU performance per vCore than the Gen 5 hardware. Overall, the 72 vCore size can provide more CPU performance than the 80 vCore Gen 5 by providing a lower CPU latency and higher clock speeds. The Fsv2 series does have less memory and tempdb per vCore than Gen 5, so that will have to be taken into consideration.
In the business-critical provisioned tier, you can access the M-series which is memory optimized. The M-series offers 29GB per vCore compared to the 5.1GB per vCore in the ‘balance memory and compute’ configuration. With the M-series you can scale vCore up to 128 which would provide up to 3.7TB of memory. To enable the M-series, you currently must be in a Pay-As-You-Go or Enterprise Agreement and open a support request. Even then, the M-Series is currently only available in East US, North Europe, West Europe, West US 2, and may also have limited availability in additional regions.
Conclusion
Azure SQL Database is a feature rich database platform that offers a wide range of options for compute and scale. Customers can configure compute for a single database or elastic pool using DTUs or vCores. For databases with a large storage requirement or read scale out, Hyperscale can be utilized. For customers with varying workload requirements, serverless can be used to automatically scale up and down as their workload demands change. New to Azure SQL Database is the preview feature of a compute optimized and memory optimized hardware configuration for those customers that need lower latency CPU or those with a high memory to CPU requirement.
To learn more about Azure resources, check out my previous articles:
