Manage elastic pools in Azure SQL Database
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
- To learn more about design patterns for SaaS applications using elastic pools, see Design Patterns for Multi-tenant SaaS Applications with Azure SQL Database.
- For a SaaS tutorial using elastic pools, see Introduction to the Wingtip SaaS application.