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.
All pool settings can be found in one place: the Configure pool blade. To get here, find an elastic pool in the Azure 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:
- 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
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.
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.
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.
|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.|
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.
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.
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.
|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.|
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.
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.
|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.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 SQL Database or Azure SQL Data Warehouse. If logged on to the master database in a server, returns information on all databases. For Azure SQL Data Warehouse, you must be connected to the master database.|
To create and manage SQL Database elastic pools and pooled databases, use these REST API requests.
|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.|
- 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.