Edit

Scale elastic pool resources in Azure SQL Database

  • Article
  • 7 minutes to read

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 the using:

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 long running transactions running when connections are dropped, the duration of this step may take longer in order to recover aborted transactions. Accelerated Database Recovery can reduce the impact from aborting long running transactions.

Important

No data is lost during any step in the workflow.

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:

Service tier Basic single database,
Standard (S0-S1)		 Basic elastic pool,
Standard (S2-S12),
General Purpose single database or elastic pool		 Premium or Business Critical single database or elastic pool Hyperscale
Basic single database,
Standard (S0-S1)		 •  Constant time latency independent of space used
•  Typically, less than 5 minutes		 •  Latency proportional to database space used due to data copying
•  Typically, less than 1 minute per GB of space used		 •  Latency proportional to database space used due to data copying
•  Typically, less than 1 minute per GB of space used		 •  Latency proportional to database space used due to data copying
•  Typically, less than 1 minute per GB of space used
Basic elastic pool,
Standard (S2-S12),
General Purpose single database or elastic pool		 •  Latency proportional to database space used due to data copying
•  Typically, less than 1 minute per GB of space used		 •  For single databases, constant time latency independent of space used
•  Typically, less than 5 minutes for single databases
•  For elastic pools, proportional to the number of databases		 •  Latency proportional to database space used due to data copying
•  Typically, less than 1 minute per GB of space used		 •  Latency proportional to database space used due to data copying
•  Typically, less than 1 minute per GB of space used
Premium or Business Critical single database or elastic pool •  Latency proportional to database space used due to data copying
•  Typically, less than 1 minute per GB of space used		 •  Latency proportional to database space used due to data copying
•  Typically, less than 1 minute per GB of space used		 •  Latency proportional to database space used due to data copying
•  Typically, less than 1 minute per GB of space used		 •  Latency proportional to database space used due to data copying
•  Typically, less than 1 minute per GB of space used
Hyperscale N/A N/A N/A •  Constant time latency independent of space used
•  Typically, less than 2 minutes

Note

  • In the case of changing the service tier or rescaling compute for an elastic pool, the summation of space used across all databases in the pool should be used to calculate the estimate.
  • In the case of moving a database to/from an elastic pool, only the space used by the database impacts the latency, not the space used by the elastic pool.
  • For Standard and General Purpose elastic pools, latency of moving a database in/out of an elastic pool or between elastic pools will be proportional to database size if the elastic pool is using Premium File Share (PFS) storage. To determine if a pool is using PFS storage, execute the following query in the context of any database in the pool. If the value in the AccountType column is PremiumFileStorage or PremiumFileStorage-ZRS, the pool is using PFS storage.
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');

Tip

To monitor in-progress operations, see: Manage operations using the SQL REST API, Manage operations using CLI, Monitor operations using T-SQL and these two PowerShell commands: Get-AzSqlDatabaseActivity and Stop-AzSqlDatabaseActivity.

Additional considerations when changing service tier or rescaling compute size

  • When downsizing vCores or eDTUs for an elastic pool, the pool used space must be smaller than the maximum allowed size of the target service tier and pool eDTUs.
  • When rescaling eDTUs for an elastic pool, an extra storage cost applies if (1) the storage max size of the pool is supported by the target pool, and (2) the storage max size exceeds the included storage amount of the target pool. For example, if a 100 eDTU Standard pool with a max size of 100 GB is downsized to a 50 eDTU Standard pool, then an extra storage cost applies since target pool supports a max 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 database max 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

Important

Under some circumstances, you may need to shrink a database to reclaim unused space. For more information, see Manage file space in Azure SQL Database.

vCore-based purchasing model

  • Storage can be provisioned up to the max size limit:

    • For storage in the Standard or General Purpose service tiers, increase or decrease size in 10-GB increments
    • For storage in the Premium or Business Critical service tiers, increase or decrease size in 250-GB increments

  • Storage for an elastic pool can be provisioned by increasing or decreasing its max size.

  • The price of storage for an elastic pool is the storage amount multiplied by the storage unit price of the service tier. For details on the price of extra storage, see SQL Database pricing.

Important

Under some circumstances, you may need to shrink a database to reclaim unused space. For more information, see Manage file space in Azure SQL Database.

DTU-based purchasing model

Important

Under some circumstances, you may need to shrink a database to reclaim unused space. For more information, see Manage file space in Azure SQL Database.

Next steps

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