Moving a pooled database in SQL Database from one SQL elastic pool into another SQL elastic pool using PowerShell

This process allows you to adjust resource allocation and optimize cost and performance based on changing needs.
Moving a database from one SQL Elastic Pool to another SQL Elastic Pool in Azure SQL Database can offer several benefits.
Here are some advantages:

• By moving a database to an elastic pool with different performance levels, you can optimize costs. For instance, if a database’s resource needs change, you can move it to a pool with a lower or higher DTU or vCore allocation to better match its requirements based on current workloads.
• If you have multiple databases with varying workloads, redistributing them among different pools can help balance the load and improve overall performance.
• Elastic pools allow for easy scaling of resources based on demand. By moving a database to a different pool, you can better align its resources with its scaling needs without impacting other databases.
• In cases where specific compliance or regulatory requirements necessitate different performance or resource configurations, moving databases to appropriately configured pools can help meet these needs.

By taking advantage of these benefits, you can make sure your Azure SQL Database setups are optimized for performance, cost, and management, which will improve the overall efficiency of your database operations.

Now I’ll guide you through a step-by-step tutorial showing how you can do this easily, practically, and automatically using PowerShell with Az Module. Let’s do it!

All variables names and values are examples, you MUST use values by your requiriments.

• Defining Variables:

# Use this command to get your subscriptionID
Get-AzSubscription

$SubscriptionId = '54356645-e5a5656-fd56-56hg732dgc'  #Your SubID HERE
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = "westus"

# Set elastic pool names
$firstPoolName = "MyFirstPool"
$secondPoolName = "MySecondPool"

# Set an admin login and password for your server
$adminSqlLogin = "admin"
$password = "Password#12345"

# The logical server name has to be unique in the system
$serverName = "server-$(Get-Random)"

# The sample database names
$firstDatabaseName = "Database1"
$secondDatabaseName = "Database2"

# The ip address range that you want to allow to access your server
$startIp = "0.0.0.0"
$endIp = "0.0.0.0"

• Creating a credential to Azure Account:

$cred = $(New-Object -TypeName 
System.Management.Automation.PSCredential -ArgumentList 'admin', 
$(ConvertTo-SecureString -String 'Password#12345' -AsPlainText -Force))

• Set Subscription:

Set-AzContext -SubscriptionId $subscriptionId 

• Create a new resource group:

$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location

• Create a new server with a system wide unique server name:

$server = New-AzSqlServer -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -Location $location `
    -SqlAdministratorCredentials $cred

• Create a firewall rule that allows access from specified IP range:

$serverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp

• Create two elastics databases pools:

$firstPool = New-AzSqlElasticPool -ResourceGroupName $resourceGroupName `
    -ServerName $servername `
    -ElasticPoolName $firstPoolName `
    -Edition "Standard" `
    -Dtu 50 `
    -DatabaseDtuMin 10 `
    -DatabaseDtuMax 20

$secondPool = New-AzSqlElasticPool -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -ElasticPoolName $secondPoolName `
    -Edition "Standard" `
    -Dtu 50 `
    -DatabaseDtuMin 10 `
    -DatabaseDtuMax 50

• Create a blank Databases in the first pool:

$firstDatabase = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $firstDatabaseName `
    -ElasticPoolName $firstPoolName

• Create a blank Databases in the second pool:

$secondDatabase = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $secondDatabaseName `
    -ElasticPoolName $secondPoolName

• Move database from first pool to the second pool:

$firstDatabase = Set-AzSqlDatabase -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $firstDatabaseName `
    -ElasticPoolName $secondPoolName

Finish! Now you have your first database on Second Elastic pool.

With the script bellow, you can use to remove database from Elastic pool to standalone database.

• Move the database into standalone performance level:

$firstDatabase = Set-AzSqlDatabase -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $firstDatabaseName `
    -RequestedServiceObjectiveName "S0"

If you’d created an enviroment to deploy a test, bellow is the script to drop all resources created with and in resource group to save costs and money.

• Clean up deployment:

Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

Moving a database between SQL Elastic Pools in Azure SQL Database allows you to better align resource allocation with your performance and cost needs. By switching to a different pool, you can optimize costs based on the database’s resource demands, balance workloads across multiple databases, and ensure that your resources scale effectively. This flexibility also helps meet specific compliance requirements.

By following these steps, you can efficiently manage and optimize your SQL database resources in Azure.