Scale elastic pool resources in Azure SQL Database

Applies to: Azure SQL Database

This article describes how to scale the compute and storage resources available for elastic pools and pooled databases in Azure SQL Database.

Change compute resources (vCores or DTUs)

After initially picking the number of vCores or eDTUs, you can scale an elastic pool up or down dynamically based on actual experience using any of the below methods:

• Azure portal
• PowerShell
• Azure CLI
• REST API

Impact of changing service tier or rescaling compute size

Changing the service tier or compute size of an elastic pool follows a similar pattern as for single databases and mainly involves the service performing the following steps:

1. Create new compute instance for the elastic pool

   A new compute instance for the elastic pool is created with the requested service tier and compute size. For some combinations of service tier and compute size changes, a replica of each database must be created in the new compute instance, which involves copying data and can strongly influence the overall latency. Regardless, the databases remain online during this step, and connections continue to be directed to the databases in the original compute instance.

2. Switch routing of connections to new compute instance

   Existing connections to the databases in the original compute instance are dropped. Any new connections are established to the databases in the new compute instance. For some combinations of service tier and compute size changes, database files are detached and reattached during the switch. Regardless, the switch can result in a brief service interruption when databases are unavailable generally for less than 30 seconds and often for only a few seconds. If there are active long running transactions when connections are dropped, the duration of this step might take longer in order to recover aborted transactions. Accelerated Database Recovery can reduce the impact from aborting long running transactions.

Latency of changing service tier or rescaling compute size

The estimated latency to change the service tier, scale the compute size of a single database or elastic pool, move a database in/out of an elastic pool, or move a database between elastic pools is parameterized as follows:

SELECT s.file_id,
       s.type_desc,
       s.name,
       FILEPROPERTYEX(s.name, 'AccountType') AS AccountType
FROM sys.database_files AS s
WHERE s.type_desc IN ('ROWS', 'LOG');

Additional considerations when changing service tier or rescaling compute size

• When you reduce vCores or eDTUs for an elastic pool, the pool used space must be smaller than the max data size limit of the target service tier and pool compute.
• When you increase eDTUs for an elastic pool, an extra storage cost might apply if:
  • The max data size of the pool is supported by the target pool, and
  • The max data size of the pool exceeds the included storage amount of the target pool.
• For example, if a 100 eDTU Standard pool with a max data size of 100 GB is downsized to a 50 eDTU Standard pool, then an extra storage cost applies since target pool supports a max data size of 100 GB and its included storage amount is only 50 GB. So, the extra storage amount is 100 GB - 50 GB = 50 GB. For pricing of extra storage, see SQL Database pricing. If the actual amount of space used is less than the included storage amount, then this extra cost can be avoided by reducing the max data size to the included amount.

Billing during rescaling

You are billed for each hour a database exists using the highest service tier + compute size that applied during that hour, regardless of usage or whether the database was active for less than an hour. For example, if you create a single database and delete it five minutes later your bill reflects a charge for one database hour.

Change elastic pool storage size

The storage size (max data size) for the elastic pool can be specified using the Azure portal, PowerShell, the Azure CLI, or the REST API. When you increase the max data size of the elastic pool, the value specified cannot exceed the max data size limit of the pool's service objective. When decreasing the max data size, the new value specified has to be equal to or greater than the summation of space allocated across all databases in the pool.

vCore-based purchasing model

• The storage size (max data size) for elastic pools in the General Purpose or Business Critical tiers can be specified up to the max data size limits specified in Resource limits for elastic pools using the vCore purchasing model. The max data size for the elastic pool can be increased or decreased in multiples of 1 GB.
• The price of storage for an elastic pool is the max data size specified, multiplied by the storage unit price of the service tier. For details on storage pricing, see SQL Database pricing.

DTU-based purchasing model

• The eDTU price for an elastic pool includes a certain amount of storage at no additional cost. Extra data storage beyond the included amount can be provisioned for an additional cost up to the max data size limit corresponding to the eDTUs provisioned. For included storage amounts and max data size limits, see Resources limits for elastic pools using the DTU purchasing model.
• The price of extra storage for an elastic pool is the extra storage amount multiplied by the extra storage unit price of the service tier. For details on the price of extra storage, see SQL Database pricing.
• Valid values for the max data size for a Standard or Premium tier elastic pool can be one of these values: 50 GB, 100 GB, 150 GB, 200 GB, 250 GB, 300 GB, 400 GB, 500 GB, 750 GB, 800 GB, 1024 GB, 1200 GB, 1280 GB, 1536 GB, 1600 GB, 1792 GB, 2000 GB, 2048 GB, 2304 GB, 2500 GB, 2560 GB, 2816 GB, 3000 GB, 3072 GB, 3328 GB, 3584 GB, 3840 GB, 4096 GB. The max data size specified cannot exceed the max data size limit specified for the eDTUs provisioned.

Monitor or cancel scaling changes

A service tier change or compute rescaling operation can be monitored and canceled.

Login-AzAccount -Environment AzureChinaCloud
$SubscriptionID = "<YourSubscriptionIdHere>"
Select-AzSubscription -SubscriptionName $SubscriptionID

Get-AzSqlElasticPoolActivity -ResourceGroupName "ResourceGroup01" -ElasticPoolName "Pool01" ServerName "Server01" 

Stop-AzSqlElasticPoolActivity -ResourceGroupName "ResourceGroup01" -ElasticPoolName "Pool01" -ServerName "Server01" -OperationId af97005d-9243-4f8a-844e-402d1cc855f5

$resourceGroupName = "<resource group name>"
$serverName = "<server name>"
$elasticPoolName = "<elastic pool name>"
az sql elastic-pool op list --resource-group $resourceGroupName --server $serverName --elastic-pool $elasticPoolName --query "[?state=='IN_PROGRESS'].name" --out tsv

$resourceGroupName = "<resource group name>"
$serverName = "<server name>"
$elasticPoolName = "<elastic pool name>"
$operationId = (az sql elastic-pool op list --resource-group $resourceGroupName --server $serverName --elastic-pool $elasticPoolName --query "[?state=='IN_PROGRESS'].name" --out tsv)
if (-not [string]::IsNullOrEmpty($operationName)) {
    (az sql elastic-pool op cancel --resource-group $resourceGroupName --server $serverName --elastic-pool $elasticPoolName --name $operationId)
        "Operation " + $operationName + " has been canceled"
}
else {
    "No management operation on the elastic pool found"
}

Permissions

To scale an elastic pool via the Azure portal, PowerShell, Azure CLI, or REST API, Azure RBAC permissions are needed, specifically the Contributor, SQL DB Contributor role, or SQL Server Contributor Azure RBAC roles. For more information, see Azure RBAC built-in roles.

Related content

For overall resource limits, see SQL Database vCore-based resource limits - elastic pools and SQL Database DTU-based resource limits - elastic pools.