Edit

Manage elastic pools in Azure SQL Database

  • Article

Applies to: Azure SQL Database

With an elastic pool, you determine the amount of resources that the elastic pool requires to handle the workload of its databases, and the amount of resources for each pooled database.

Azure portal

All pool settings can be found in one place: the Configure pool pane. To get here, find an elastic pool in the Azure portal and select Configure either from the Overview page menu, or from the resource menu under Settings.

From here you can make any combination of the following changes and save them all in one batch:

  • Change the service tier of the pool.
  • Scale the performance (DTU or vCores) and storage up or down.
  • Add or remove databases to/from the pool.
  • Set a min (guaranteed) and max performance limit for the databases in the pools.
  • Review the cost summary to view any changes to your bill as a result of your new selections.

PowerShell

Note

This article uses the Azure Az PowerShell module, which is the recommended PowerShell module for interacting with Azure. To get started with the Az PowerShell module, see Install Azure PowerShell. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.

Important

The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. For these cmdlets, see AzureRM.Sql. The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

To create and manage SQL Database elastic pools and pooled databases with Azure PowerShell, use the following PowerShell cmdlets. If you need to install or upgrade PowerShell, see Install Azure PowerShell module. To create and manage the servers for an elastic pool, see Create and manage servers. To create and manage firewall rules, see Create and manage firewall rules using PowerShell.

Tip

For PowerShell example scripts, see Create elastic pools and move databases between pools and out of a pool using PowerShell and Use PowerShell to monitor and scale a SQL elastic pool in Azure SQL Database.

Cmdlet Description
New-AzSqlElasticPool Creates an elastic pool.
Get-AzSqlElasticPool Gets elastic pools and their property values.
Set-AzSqlElasticPool Modifies properties of an elastic pool For example, use the StorageMB property to modify the max storage of an elastic pool.
Remove-AzSqlElasticPool Deletes an elastic pool.
Get-AzSqlElasticPoolActivity Gets the status of operations on an elastic pool
Stop-AzSqlElasticPoolActivity Cancels the asynchronous update operation on an elastic pool.
New-AzSqlDatabase Creates a new database in an existing pool or as a single database.
Get-AzSqlDatabase Gets one or more databases.
Set-AzSqlDatabase Sets properties for a database, or moves an existing database into, out of, or between elastic pools.
Remove-AzSqlDatabase Removes a database.

Tip

Creation of many databases in an elastic pool can take time when done using the portal or PowerShell cmdlets that create only a single database at a time. To automate creation into an elastic pool, see CreateOrUpdateElasticPoolAndPopulate.

Azure CLI

To create and manage SQL Database elastic pools with Azure CLI, use the following Azure CLI SQL Database commands. Use the Cloud Shell to run Azure CLI in your browser, or install it on macOS, Linux, or Windows.

Tip

For Azure CLI example scripts, see Use CLI to move a database in SQL Database in a SQL elastic pool and Use Azure CLI to scale a SQL elastic pool in Azure SQL Database.

Cmdlet Description
az sql elastic-pool create Creates an elastic pool.
az sql elastic-pool list Returns a list of elastic pools in a server.
az sql elastic-pool list-dbs Returns a list of databases in an elastic pool.
az sql elastic-pool list-editions Also includes available pool DTU settings, storage limits, and per database settings. In order to reduce verbosity, additional storage limits and per database settings are hidden by default.
az sql elastic-pool update Updates an elastic pool.
az sql elastic-pool delete Deletes the elastic pool.

Transact-SQL (T-SQL)

To create and move databases within existing elastic pools or to return information about an SQL Database elastic pool with Transact-SQL, use the following T-SQL commands. You can issue these commands using the Azure portal, SQL Server Management Studio, Visual Studio Code, or any other program that can connect to a server and pass Transact-SQL commands. To create and manage firewall rules using T-SQL, see Manage firewall rules using Transact-SQL.

Important

You cannot create, update, or delete an Azure SQL Database elastic pool using Transact-SQL. You can add or remove databases from an elastic pool, and you can use DMVs to return information about existing elastic pools.

Command Description
CREATE DATABASE (Azure SQL Database) Creates a new database in an existing pool or as a single database. You must be connected to the master database to create a new database.
ALTER DATABASE (Azure SQL Database) Move a database into, out of, or between elastic pools.
DROP DATABASE (Transact-SQL) Deletes a database.
sys.elastic_pool_resource_stats (Azure SQL Database) Returns resource usage statistics for all the elastic pools on a server. For each elastic pool, there is one row for each 15 second reporting window (four rows per minute). This includes CPU, IO, Log, storage consumption and concurrent request/session utilization by all databases in the pool.
sys.dm_elastic_pool_resource_stats (Azure SQL Database) Returns resource usage statistics for the elastic pool containing the current database on an Azure SQL Database logical server. This includes CPU, Data IO, Log IO, storage consumption and concurrent request/session utilization by the pool. This data is retained for roughly 40 minutes.
sys.database_service_objectives (Azure SQL Database) Returns the edition (service tier), service objective (pricing tier), and elastic pool name, if any, for a database in Azure SQL Database or Azure Synapse Analytics. If logged on to the master database in a server, returns information on all databases. For Azure Synapse Analytics, you must be connected to the master database.

REST API

To create and manage SQL Database elastic pools and pooled databases, use these REST API requests.

Command Description
Elastic pools - Create or update Creates a new elastic pool or updates an existing elastic pool.
Elastic pools - Delete Deletes the elastic pool.
Elastic pools - Get Gets an elastic pool.
Elastic pools - List by server Returns a list of elastic pools in a server.
Elastic pools - Update Updates an existing elastic pool.
Elastic pool activities Returns elastic pool activities.
Elastic pool database activities Returns activity on databases inside of an elastic pool.
Databases - Create or update Creates a new database or updates an existing database.
Databases - Get Gets a database.
Databases - List by elastic pool Returns a list of databases in an elastic pool.
Databases - List by server Returns a list of databases in a server.
Databases - Update Updates an existing database.

Related content