Optimizing Azure SQL Database Costs with Hyperscale Elastic Pools

In today's data-driven landscape, organizations often grapple with managing large-scale databases efficiently while keeping costs in check. Azure SQL Database offers a solution tailored for such scenarios: the Hyperscale service tier. This tier is particularly beneficial for applications requiring expansive storage without proportionally high compute resources

One key benefit of the Hyperscale service tier over the General Purpose or Business Critical tiers is the decoupling of storage from compute resources. In Hyperscale, storage can grow to much larger sizes without necessitating an increase in DTU or vCore levels. For example, databases in Elastic Pools may approach the 3 TB storage limit at their current vCore tier. While additional vCores would be required to extend storage capacity beyond this limit in traditional tiers, Hyperscale allows storage to scale independently, eliminating the need for increased compute resources solely for additional storage capacity

Understanding Hyperscale Elastic Pools
Elastic pools in Azure SQL Database allow multiple databases to share a set of resources, optimizing performance and cost. The Hyperscale service tier enhances this model by providing:​

• Massive Storage Capacity: Support for databases up to 128 TB, accommodating substantial data growth. ​
• Independent Scaling: Decoupled compute and storage resources, enabling tailored performance configurations without unnecessary cost escalations.​
• Rapid Scaling and Restore: Quick adaptation to workload changes and swift backup operations, ensuring minimal downtime

Architecture
Traditionally, the architecture of a standalone Hyperscale database consists of three main independent components: Compute, Storage ("Page Servers"), and the log ("Log Service"). When you create an elastic pool for your Hyperscale databases, the databases within the pool share compute and log resources. Additionally, if you choose to configure high availability, then each high availability pool is created with an equivalent and independent set of compute and log resources.
The following describes the architecture of an elastic pool for Hyperscale databases:

• A Hyperscale elastic pool consists of a primary pool that hosts primary Hyperscale databases and, if configured, up to four additional high-availability pools.
• Primary Hyperscale databases hosted in the primary elastic pool share the SQL Server database engine (sqlservr.exe) compute process, vCores, memory, and SSD cache.
• Configuring high availability for the primary pool creates additional high availability pools that contain read-only database replicas for the databases in the primary pool. Each primary pool can have a maximum of four high-availability replica pools. Each high-availability pool shares compute, SSD cache, and memory resources for all the secondary read-only databases in the pool.
• Hyperscale databases in the primary elastic pool all share the same log service. Since databases in the high availability pools don't have a write workload, they don't utilize the log service.
• Each Hyperscale database has its own set of page servers, and these page servers are shared between the primary database in the primary pool, and all secondary replica databases in the high availability pool.
• Geo-replicated secondary Hyperscale databases can be placed inside another elastic pool.
• Specifying ApplicationIntent=ReadOnly in your database connection string routes you to a read-only replica database in one of the high availability pools.

The diagram (in the top) shows the architecture of an elastic pool for Hyperscale databases

Cost Efficiency with Hyperscale
A notable advantage of the Hyperscale tier is its potential for significant cost reductions, especially for large databases with moderate compute demands:​

• Compute and Storage Decoupling: Unlike traditional tiers where storage size influences compute costs, Hyperscale allows independent scaling. This means you can allocate extensive storage without incurring high compute charges.​
• Real-World Savings: In practical scenarios, transitioning to Hyperscale has led to substantial savings. For instance, one of my customer experienced a 50% cost reduction by migrating to Hyperscale, benefiting from its flexible resource allocation.​

Convert non-Hyperscale databases to Hyperscale elastic pools using PowerShell
You can use PowerShell commands to convert multiple General Purpose databases and add them to an existing Hyperscale elastic pool named hsep1. For example, the following sample script performs these steps:

1. Use the Get-AzSqlElasticPoolDatabase cmdlet to list all the databases in the General Purpose elastic pool named gpep1.
2. The Where-Object cmdlet filters the list to only those database names starting with gpepdb.
3. For each database, Set-AzSqlDatabase cmdlet starts a conversion. In this case, you're implicitly requesting a conversion to the Hyperscale service tier by specifying the target Hyperscale elastic pool named hsep1.
   • The -AsJob parameter allows each of the Set-AzSqlDatabase requests to run in parallel. If you prefer to run the conversions one-by-one, you can remove the -AsJob parameter.

$dbs = Get-AzSqlElasticPoolDatabase -ResourceGroupName "myResourceGroup" -ServerName "mylogicalserver" -ElasticPoolName "gpep1" $dbs | Where-Object { $_.DatabaseName -like "gpepdb*" } | % {

Set-AzSqlDatabase -ResourceGroupName "myResourceGroup" -ServerName "mylogicalserver" -DatabaseName ($_.DatabaseName) -ElasticPoolName "hsep1" -AsJob }

Limitations
Consider the following limitations:

• Changing an existing non-Hyperscale elastic pool to the Hyperscale edition isn't supported. The conversion section provides some alternatives you can use.
• Changing the edition of a Hyperscale elastic pool to a non-Hyperscale edition isn't supported.
• In order to "reverse migrate" an eligible database, which is in a Hyperscale elastic pool, it must first be removed from the Hyperscale elastic pool. The standalone Hyperscale database can then be "reverse migrated" to a General Purpose standalone database.
• For the Hyperscale service tier, zone redundancy support can only be specified during database or elastic pool creation and can't be modified once the resource is provisioned. For more information, see Migrate Azure SQL Database to availability zone support.
• Adding a named replica into a Hyperscale elastic pool isn't supported. Attempting to add a named replica of a Hyperscale database to a Hyperscale elastic pool results in an UnsupportedReplicationOperation error. Instead, create the named replica as a single Hyperscale database.

Seamless Migration and Flexibility
Migrating to the Hyperscale tier is designed to be straightforward:

• Online Conversion: The process involves an initial data copy while the source database remains online, followed by a brief cutover period, minimizing downtime. ​
• Reversibility: If Hyperscale doesn't align with your requirements, reverse migration to other service tiers is possible, offering operational flexibility

Key Considerations
When evaluating Hyperscale for your databases:

• Workload Characteristics: Ideal for applications with large data volumes but moderate compute needs, such as archival systems or data warehouses.​
• Performance Requirements: Ensure that the performance metrics of Hyperscale align with your application's demands.​
• Cost-Benefit Analysis: Assess the potential savings against your current expenditure to determine the financial viability.​
  

Conclusion
Azure SQL Database's Hyperscale tier offers a compelling solution for managing large databases cost-effectively. Its ability to independently scale compute and storage resources provides organizations with the flexibility to optimize performance without unnecessary expenses. By carefully considering your workload requirements and leveraging Hyperscale's features, you can achieve significant cost savings while maintaining operational efficiency.​