Implementing an Azure SQL database elastic pool using PowerShell

An elastic pool is a cost-effective mechanism to group single Azure SQL databases of varying peak usage times. For example, consider 20 different SQL databases with varying usage patterns, each Standard S3 requiring 100 database throughput units (DTUs) to run. We need to pay for 100 DTUs separately. However, we can group all of them in an elastic pool of Standard S3. In this case, we only need to pay for elastic pool pricing and not for each individual SQL database.

In this recipe, we'll create an elastic pool of multiple single Azure databases.

Getting ready

In a new PowerShell window, execute the Connect-AzAccount command and follow the steps to log in to your Azure account.

How it works…

The steps for this recipe are as follows:

1. Execute the following query on an Azure SQL server:

   #create credential object for the Azure SQL Server admin credential
   $sqladminpassword = ConvertTo-SecureString 'Sql@Server@1234' -AsPlainText -Force
   $sqladmincredential = New-Object System.Management.Automation.PSCredential ('sqladmin', $sqladminpassword)
   # create the azure sql server
   New-AzSqlServer -ServerName azadesqlserver -SqlAdministratorCredentials $sqladmincredential -Location "central us" -ResourceGroupName packtade
   Execute the following query to create an elastic pool.
   #Create an elastic pool
   New-AzSqlElasticPool -ElasticPoolName adepool -ServerName azadesqlserver -Edition standard -Dtu 100 -DatabaseDtuMin 20 -DatabaseDtuMax 100 -ResourceGroupName packtade

   You should get an output as shown in the following screenshot:

   

   Figure 2.26 – Creating a new Azure elastic pool

2. Execute the following query to create and add an Azure SQL database to an elastic pool:

   #Create a new database in elastic pool
   New-AzSqlDatabase -DatabaseName azadedb1 -ElasticPoolName adepool -ServerName azadesqlserver -ResourceGroupName packtade

   You should get an output as shown in the following screenshot:

   

   Figure 2.27 – Creating a new SQL database in an elastic pool

3. Execute the following query to create a new Azure SQL database outside of the elastic pool:

   #Create a new database outside of an elastic pool
   New-AzSqlDatabase -DatabaseName azadedb2 -Edition Standard -RequestedServiceObjectiveName S3 -ServerName azadesqlserver -ResourceGroupName packtade

   You should get an output as shown in the following screenshot:

   

   Figure 2.28 – Creating a new SQL database

4. Execute the following query to add the adesqldb2 database created in the preceding step to the elastic pool:

   #Add an existing database to the elastic pool
   $db = Get-AzSqlDatabase -DatabaseName azadedb2 -ServerName azadesqlserver -ResourceGroupName packtade
   $db | Set-AzSqlDatabase -ElasticPoolName adepool

   You should get an output as shown in the following screenshot:

   

   Figure 2.29 – Adding an existing SQL database to an elastic pool

5. To verify this in the Azure portal, log in with your Azure account. Navigate to All resources | azadesqlserver | SQL elastic pools | Configure:
   

   Figure 2.30 – Viewing the elastic pool in the Azure portal

6. Execute the following command to remove an Azure SQL database from an elastic pool. To move a database out of an elastic pool, we need to set the edition and the service objective explicitly:

   #remove a database from an elastic pool
   $db = Get-AzSqlDatabase -DatabaseName azadesqldb2 -ServerName azadesqlserver -ResourceGroupName packtade
   $db | Set-AzSqlDatabase -Edition Standard -RequestedServiceObjectiveName S3

   You should get an output as shown in the following screenshot:

   

   Figure 2.31 – Removing a SQL database from an elastic pool

7. Execute the command that follows to remove an elastic pool. An elastic pool should be empty before it can be removed. Execute the following query to remove all of the databases in an elastic pool:

   # get elastic pool object 
   $epool = Get-AzSqlElasticPool -ElasticPoolName adepool -ServerName azadesqlserver -ResourceGroupName packtade
   # get all databases in an elastic pool
   $epdbs = $epool | Get-AzSqlElasticPoolDatabase
   # change the edition of all databases in an elastic pool to standard S3
   foreach($db in $epdbs) {
   $db | Set-AzSqlDatabase -Edition Standard -RequestedServiceObjectiveName S3
   }
   # Remove an elastic pool 
   $epool | Remove-AzSqlElasticPool

   Note

   The command sets the edition of the SQL databases to Standard. This is for demo purposes only. If this is to be done on production, modify the edition and service objective accordingly.

How it works…

We create an elastic pool using the New-AzSqlElasticPool command. In addition to the parameters, such as the server name, resource group name, compute model, compute generation, and edition, which are the same as when we create a new Azure SQL database, we can specify DatabaseMinDtu and DatabaseMaxDtu. DatabaseMinDtu specifies the minimum DTU that all databases can have in an elastic pool. DatabaseMaxDtu is the maximum DTU that a database can consume in an elastic pool.

Similarly, for the vCore-based purchasing model, we can specify DatabaseVCoreMin and DatabaseVCoreMax.

To add a new database to an elastic pool, specify the elastic pool name at the time of database creation using the New-AzSqlDatabase command.

To add an existing database to an elastic pool, modify the database using Set-AzSqlDatabase to specify the elastic pool name.

To remove a database from an elastic pool, modify the database using the Set-AzSqlDatabase command to specify a database edition explicitly.

To remove an elastic pool, first empty it by moving out all of the databases from the elastic pool, and then remove it using the Remove-AzSqlElasticPool command.