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 = "<Subscription-ID>"
# Set the resource group name and location for your server
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = "chinaeast2"
# Set elastic pool names
$firstPoolName = "MyFirstPool"
$secondPoolName = "MySecondPool"
# Set an admin login and password for your server
$adminSqlLogin = "<admin>"
$password = "<password>"
# 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

$adminCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force)

# Create a new server with a system-wide unique server name
$serverParams = @{
    ResourceGroupName           = $resourceGroupName
    ServerName                  = $serverName
    Location                    = $location
    SqlAdministratorCredentials = $adminCredential
}
$server = New-AzSqlServer @serverParams

# Create a server firewall rule that allows access from the specified IP range
$firewallParams = @{
    ResourceGroupName = $resourceGroupName
    ServerName        = $serverName
    FirewallRuleName  = "AllowedIPs"
    StartIpAddress    = $startIp
    EndIpAddress      = $endIp
}
$serverFirewallRule = New-AzSqlServerFirewallRule @firewallParams

# Create two elastic database pools
$firstPoolParams = @{
    ResourceGroupName = $resourceGroupName
    ServerName        = $serverName
    ElasticPoolName   = $firstPoolName
    Edition           = "Standard"
    Dtu               = 50
    DatabaseDtuMin    = 10
    DatabaseDtuMax    = 20
}
$firstPool = New-AzSqlElasticPool @firstPoolParams
$secondPoolParams = @{
    ResourceGroupName = $resourceGroupName
    ServerName        = $serverName
    ElasticPoolName   = $secondPoolName
    Edition           = "Standard"
    Dtu               = 50
    DatabaseDtuMin    = 10
    DatabaseDtuMax    = 50
}
$secondPool = New-AzSqlElasticPool @secondPoolParams

# Create two blank databases in the first pool
$firstDatabaseParams = @{
    ResourceGroupName = $resourceGroupName
    ServerName        = $serverName
    DatabaseName      = $firstDatabaseName
    ElasticPoolName   = $firstPoolName
}
$firstDatabase = New-AzSqlDatabase @firstDatabaseParams
$secondDatabaseParams = @{
    ResourceGroupName = $resourceGroupName
    ServerName        = $serverName
    DatabaseName      = $secondDatabaseName
    ElasticPoolName   = $secondPoolName
}
$secondDatabase = New-AzSqlDatabase @secondDatabaseParams

# Move the database to the second pool
$moveToPoolParams = @{
    ResourceGroupName = $resourceGroupName
    ServerName        = $serverName
    DatabaseName      = $firstDatabaseName
    ElasticPoolName   = $secondPoolName
}
$firstDatabase = Set-AzSqlDatabase @moveToPoolParams

# Move the database into a standalone performance level
$moveToStandaloneParams = @{
    ResourceGroupName             = $resourceGroupName
    ServerName                    = $serverName
    DatabaseName                  = $firstDatabaseName
    RequestedServiceObjectiveName = "S0"
}
$firstDatabase = Set-AzSqlDatabase @moveToStandaloneParams

# 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.

Related content

  • Last updated on