Manage elastic pools in 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: Manage elastic pools and pooled databases

All pool settings can be found in one place: the Configure pool blade. To get here, find an elastic pool in the portal and click Configure pool either from the top of the blade or from the resource menu on the left.

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

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

Elastic pool configuration blade

PowerShell: Manage elastic pools and pooled databases

Note

This article has been updated to use the new Azure PowerShell Az module. You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. For Az module installation instructions, see Install Azure PowerShell.

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 SQL Database servers for an elastic pool, see Create and manage SQL Database servers. To create and manage firewall rules, see Create and manage firewall rules using PowerShell.

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
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: Manage elastic pools and pooled databases

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

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: Manage pooled databases

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 an Azure SQL Database 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 in a SQL Database 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.database_service_objectives (Azure SQL Database) Returns the edition (service tier), service objective (pricing tier), and elastic pool name, if any, for an Azure SQL database or an Azure SQL Data Warehouse. If logged on to the master database in an Azure SQL Database server, returns information on all databases. For Azure SQL Data Warehouse, you must be connected to the master database.

REST API: Manage elastic pools and pooled databases

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.

Next steps