Use PowerShell to create elastic pools and move a database between them
Applies to: Azure SQL Database
This PowerShell script example creates two elastic pools, moves a pooled database in SQL Database from one SQL elastic pool into another SQL elastic pool, and then moves the pooled database out of the SQL elastic pool to be a single database in Azure SQL Database.
If you don't have an Azure trail subscription, create a trial subscription before you begin.
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.
This tutorial requires Az PowerShell 1.4.0 or later. If you need to upgrade, see Install Azure PowerShell module. You also need to run Connect-AzAccount -EnvironmentName AzureChinaCloud
to create a connection with Azure.
Sample script
# Connect-AzAccount -Environment AzureChinaCloud
$SubscriptionId = '<replace with your subscription id>'
# Set the resource group name and location for your server
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = "chinaeast"
# Set elastic pool names
$firstPoolName = "MyFirstPool"
$secondPoolName = "MySecondPool"
# Set an admin login and password for your server
$adminSqlLogin = "SqlAdmin"
$password = "<EnterYourComplexPasswordHere>"
# The logical server name has to be unique in the system
$serverName = "server-$(Get-Random)"
# The sample database names
$firstDatabaseName = "myFirstSampleDatabase"
$secondDatabaseName = "mySecondSampleDatabase"
# The ip address range that you want to allow to access your server
$startIp = "0.0.0.0"
$endIp = "0.0.0.0"
# 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 $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
# Create a server firewall rule that allows access from the specified IP range
$serverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp
# Create two elastic database 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 two blank databases in the first pool
$firstDatabase = New-AzSqlDatabase -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-DatabaseName $firstDatabaseName `
-ElasticPoolName $firstPoolName
$secondDatabase = New-AzSqlDatabase -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-DatabaseName $secondDatabaseName `
-ElasticPoolName $secondPoolName
# Move the database to the second pool
$firstDatabase = Set-AzSqlDatabase -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-DatabaseName $firstDatabaseName `
-ElasticPoolName $secondPoolName
# Move the database into a standalone performance level
$firstDatabase = Set-AzSqlDatabase -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-DatabaseName $firstDatabaseName `
-RequestedServiceObjectiveName "S0"
# Clean up deployment
# Remove-AzResourceGroup -ResourceGroupName $resourceGroupName
Clean up deployment
Use the following command to remove the resource group and all resources associated with it.
Remove-AzResourceGroup -ResourceGroupName $resourcegroupname
Script explanation
This script uses the following commands. Each command in the table links to command-specific documentation.
Command | Notes |
---|---|
New-AzResourceGroup | Creates a resource group in which all resources are stored. |
New-AzSqlServer | Creates a server that hosts databases and elastic pools. |
New-AzSqlElasticPool | Creates an elastic pool. |
New-AzSqlDatabase | Creates a database in a server. |
Set-AzSqlDatabase | Updates database properties or moves a database into, out of, or between elastic pools. |
Remove-AzResourceGroup | Deletes a resource group including all nested resources. |
Next steps
For more information on Azure PowerShell, see Azure PowerShell documentation.
Additional SQL Database PowerShell script samples can be found in the Azure SQL Database PowerShell scripts.