Monitor and manage an elastic pool with Transact-SQL

This topic shows you how to manage scalable elastic pools with Transact-SQL. You can also create and manage an Azure elastic pool the Azure portal, PowerShell, the REST API, or C#. You can also create and move databases into and out of elastic pools using Transact-SQL.

Use the Create Database (Azure SQL Database) and Alter Database(Azure SQL Database) commands to create and move databases into and out of elastic pools. The elastic pool must exist before you can use these commands. These commands affect only databases. Creation of new pools and the setting of pool properties (such as min and max eDTUs) cannot be changed with T-SQL commands.

Create a pooled database in an elastic pool

Use the CREATE DATABASE command with the SERVICE_OBJECTIVE option.

CREATE DATABASE db1 ( SERVICE_OBJECTIVE = ELASTIC_POOL (name = [S3M100] ));
-- Create a database named db1 in an elastic named S3M100.

All databases in an elastic pool inherit the service tier of the elastic pool (Basic, Standard, Premium).

Move a database between elastic pools

Use the ALTER DATABASE command with the MODIFY and set SERVICE_OBJECTIVE option as ELASTIC_POOL. Set the name to the name of the target pool.

ALTER DATABASE db1 MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL (name = [PM125] ));
-- Move the database named db1 to an elastic named P1M125  

Move a database into an elastic pool

Use the ALTER DATABASE command with the MODIFY and set SERVICE_OBJECTIVE option as ELASTIC_POOL. Set the name to the name of the target pool.

ALTER DATABASE db1 MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL (name = [S3100] ));
-- Move the database named db1 to an elastic named S3100.

Move a database out of an elastic pool

Use the ALTER DATABASE command and set the SERVICE_OBJECTIVE to one of the performance levels (such as S0 or S1).

ALTER DATABASE db1 MODIFY ( SERVICE_OBJECTIVE = 'S1');
-- Changes the database into a stand-alone database with the service objective S1.

List databases in an elastic pool

Use the sys.database_service _objectives view to list all the databases in an elastic pool. Log in to the master database to query the view.

SELECT d.name, slo.*  
FROM sys.databases d 
JOIN sys.database_service_objectives slo  
ON d.database_id = slo.database_id
WHERE elastic_pool_name = 'MyElasticPool'; 

Get resource usage data for an elastic pool

Use the sys.elastic_pool _resource _stats view to examine the resource usage statistics of an elastic pool on a logical server. Log in to the master database to query the view.

SELECT * FROM sys.elastic_pool_resource_stats 
WHERE elastic_pool_name = 'MyElasticPool'
ORDER BY end_time DESC;

Get resource usage for a pooled database

Use the sys.dm_ db_ resource_stats view or sys.resource _stats view to examine the resource usage statistics of a database in an elastic pool. This process is similar to querying resource usage for a single database.

Next steps

After creating an elastic pool, you can manage elastic databases in the pool by creating elastic jobs. Elastic jobs facilitate running T-SQL scripts against any number of databases in the pool. For more information, see Elastic database jobs overview.

See Scaling out with Azure SQL Database: use elastic database tools to scale out, move data, query, or create transactions.