Hyperscale elastic pools overview in Azure SQL Database

  • Article

Applies to: Azure SQL Database

This article provides an overview of Hyperscale elastic pools in Azure SQL Database.

An Azure SQL Database elastic pool enables software-as-a-service (SaaS) developers to optimize the price-performance ratio for a group of databases within a prescribed budget while delivering performance elasticity for each database. Azure SQL Database Hyperscale elastic pools introduces a shared resource model for Hyperscale databases.

For examples to create, scale, or move databases into a Hyperscale elastic pool by using the Azure CLI or PowerShell, review Working with Hyperscale elastic pools using command-line tools

Overview

Deploy your Hyperscale database to an elastic pool to share resources between databases within the pool and optimize the cost of having multiple databases with different usage patterns.

Scenarios to use an elastic pool with your Hyperscale databases:

  • When you need to scale the compute resources allocated to the elastic pool up or down in a predictable amount of time, independent of the amount of allocated storage.
  • When you want to scale out the compute resources allocated to the elastic pool by adding one or more read-scale replicas.
  • If you want to use high transaction log throughput for write-intensive workloads, even with lower compute resources.

Adding non-Hyperscale databases to a Hyperscale elastic pool converts the databases to the Hyperscale service tier.

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 following diagram shows the architecture of an elastic pool for Hyperscale databases:

Diagram showing the Hyperscale elastic pool architecture.

Manage Hyperscale elastic pool databases

You can use the same commands to manage your pooled Hyperscale databases as pooled databases in the other service tiers. Just be sure to specify Hyperscale for the edition when creating your Hyperscale elastic pool.

The only difference is the ability to modify the number of high availability (H/A) replicas for an existing Hyperscale elastic pool. To do so:

You can use the following client tools to manage your Hyperscale databases in an elastic pool:

Convert non-Hyperscale databases to Hyperscale elastic pools

When converting a database to Hyperscale, you can add the database to an existing Hyperscale elastic pool. For these conversions, the Hyperscale elastic pool needs to exist on the same logical server as the source database.

When converting databases to Hyperscale elastic pools, be aware of the maximum number of databases per Hyperscale elastic pool.

Convert non-Hyperscale databases to Hyperscale elastic pools using T-SQL

You can use T-SQL commands to convert multiple General Purpose databases and add them to an existing Hyperscale elastic pool named hsep1:

ALTER DATABASE gpepdb1 MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL(NAME = [hsep1]))
ALTER DATABASE gpepdb2 MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL(NAME = [hsep1]))
ALTER DATABASE gpepdb3 MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL(NAME = [hsep1]))
ALTER DATABASE gpepdb4 MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL(NAME = [hsep1]))

In this example, you're implicitly requesting a conversion from General Purpose to Hyperscale, by specifying that the target SERVICE_OBJECTIVE is a Hyperscale elastic pool. Each of the above commands starts converting the respective General Purpose database to Hyperscale. These ALTER DATABASE commands return quickly and don't wait for the conversion to complete. In the example shown, you would have four such conversions from General Purpose to Hyperscale running in parallel.

You can query the sys.dm_operation_status dynamic management view to monitor the status of these background conversion operations.

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 }

In addition to the sys.dm_operation_status dynamic management view, you can use the PowerShell cmdlet Get-AzSqlDatabaseActivity to monitor the status of these background conversion operations.

Resource limits

The following lists the supported limits for working with Hyperscale databases within elastic pools:

  • Maximum supported data size per database: 100 TB.
  • Maximum supported total data size across databases in the pool: 100 TB.
  • Maximum supported transaction log throughput per database: 100 MB.
  • Maximum supported total transaction log throughput across databases in the pool: 131.25 MB/second.
  • Each Hyperscale elastic pool can have up to 25 databases.

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.
  • 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.

Known issues

Issue Recommendation
In rare cases, you might get the error 45122 - This Hyperscale database cannot be added into an elastic pool at this time. In case of any questions, please contact Microsoft support, when trying to move / restore / copy a Hyperscale database into an elastic pool. This limitation is due to implementation-specific details. If this error is blocking you, raise a support incident and request help.

Related content