Use PowerShell to configure active geo-replication for a pooled Azure SQL database

This PowerShell script example configures active geo-replication for an Azure SQL database in an elastic pool and fails it over to the secondary replica of the Azure SQL database.

This sample requires the Azure PowerShell module version 4.0 or later. Run Get-Module -ListAvailable AzureRM to find the version. If you need to install or upgrade, see Install Azure PowerShell module.

Run Login-AzureRmAccount to create a connection with Azure.

Sample scripts

# Login-AzureRmAccount
# Set the resource group name and location for your serverw
$primaryresourcegroupname = "myPrimaryResourceGroup-$(Get-Random)"
$secondaryresourcegroupname = "mySecondaryResourceGroup-$(Get-Random)"
$primarylocation = "westus2"
$secondarylocation = "southcentralus"
# The logical server names have to be unique in the system
$primaryservername = "primary-server-$(Get-Random)"
$secondaryservername = "secondary-server-$(Get-Random)"
# Set an admin login and password for your servers
$adminlogin = "ServerAdmin"
$password = "ChangeYourAdminPassword1"
# The sample database name
$databasename = "mySampleDatabase"
# The ip address ranges that you want to allow to access your servers
$primarystartip = "0.0.0.0"
$primaryendip = "0.0.0.0"
$secondarystartip = "0.0.0.0"
$secondaryendip = "0.0.0.0"
# The elastic pool names
$primarypoolname = "PrimaryPool"
$secondarypoolname = "SecondaryPool"

# Create two new resource groups
$primaryresourcegroupname = New-AzureRmResourceGroup -Name $primaryresourcegroupname -Location $primarylocation
$secondaryresourcegroupname = New-AzureRmResourceGroup -Name $secondaryresourcegroupname -Location $secondarylocation

# Create two new logical servers with a system wide unique server name

$primaryserver = New-AzureRmSqlServer -ResourceGroupName $primaryresourcegroupname `
    -ServerName $primaryservername `
    -Location $primarylocation `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
$secondaryserver = New-AzureRmSqlServer -ResourceGroupName $secondaryresourcegroupname `
    -ServerName $secondaryservername `
    -Location $secondarylocation `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

# Create a server firewall rule for each server that allows access from the specified IP range
$primaryserverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $primaryresourcegroupname `
    -ServerName $primaryservername `
    -FirewallRuleName "AllowedIPs" -StartIpAddress $primarystartip -EndIpAddress $primaryendip
$secondaryserverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $secondaryresourcegroupname `
    -ServerName $secondaryservername `
    -FirewallRuleName "AllowedIPs" -StartIpAddress $secondarystartip -EndIpAddress $secondaryendip

# Create a pool in each of the servers
$primarypool = New-AzureRmSqlElasticPool -ResourceGroupName $primaryresourcegroupname `
    -ServerName $primaryservername `
    -ElasticPoolName $primarypoolname `
    -Edition "Standard" `
    -Dtu 50 `
    -DatabaseDtuMin 10 `
    -DatabaseDtuMax 50
$secondarypool = New-AzureRmSqlElasticPool -ResourceGroupName $secondaryresourcegroupname `
    -ServerName $secondaryservername `
    -ElasticPoolName $secondarypoolname `
    -Edition "Standard" `
    -Dtu 50 `
    -DatabaseDtuMin 10 `
    -DatabaseDtuMax 50

# Create a blank database in the pool on the primary server
$database = New-AzureRmSqlDatabase  -ResourceGroupName $primaryresourcegroupname `
    -ServerName $primaryservername `
    -DatabaseName $databasename `
    -ElasticPoolName $primarypoolname

# Establish Active Geo-Replication
$database = Get-AzureRmSqlDatabase -ResourceGroupName $primaryresourcegroupname `
    -ServerName $primaryservername `
    -DatabaseName $databasename
$database | New-AzureRmSqlDatabaseSecondary -PartnerResourceGroupName $secondaryresourcegroupname `
    -PartnerServerName $secondaryservername `
    -SecondaryElasticPoolName $primarypoolname `
    -AllowConnections "All"

# Initiate a planned failover
$database = Get-AzureRmSqlDatabase -ResourceGroupName $secondaryresourcegroupname `
    -ServerName $secondaryservername `
    -DatabaseName $databasename 
$database | Set-AzureRmSqlDatabaseSecondary -PartnerResourceGroupName $primaryresourcegroupname -Failover

    
# Monitor Geo-Replication config and health after failover
$database = Get-AzureRmSqlDatabase -ResourceGroupName $secondaryresourcegroupname `
    -ServerName $secondaryservername `
    -DatabaseName $databasename
$database | Get-AzureRmSqlDatabaseReplicationLink -PartnerResourceGroupName $primaryresourcegroupname `
    -PartnerServerName $primaryservername

# Clean up deployment 
# Remove-AzureRmResourceGroup -ResourceGroupName $primaryresourcegroupname
# Remove-AzureRmResourceGroup -ResourceGroupName $secondaryresourcegroupname

Clean up deployment

After the script sample has been run, the following command can be used to remove the resource group and all resources associated with it.

Remove-AzureRmResourceGroup -ResourceGroupName $primaryresourcegroupname
Remove-AzureRmResourceGroup -ResourceGroupName $secondaryresourcegroupname

Script explanation

This script uses the following commands. Each command in the table links to command specific documentation.

Command Notes
New-AzureRmResourceGroup Creates a resource group in which all resources are stored.
New-AzureRmSqlServer Creates a logical server that hosts a database or elastic pool.
New-AzureRmSqlElasticPool Creates an elastic pool within a logical server.
New-AzureRmSqlDatabase Creates a database in a logical server as a single or a pooled database.
Set-AzureRmSqlDatabase Updates database properties or moves a database into, out of, or between elastic pools.
New-AzureRmSqlDatabaseSecondary Creates a secondary database for an existing database and starts data replication.
Get-AzureRmSqlDatabase Gets one or more databases.
Set-AzureRmSqlDatabaseSecondary Switches a secondary database to be primary in order to initiate failover.
Get-AzureRmSqlDatabaseReplicationLink Gets the geo-replication links between an Azure SQL Database and a resource group or SQL Server.
Remove-AzureRmResourceGroup Deletes a resource group including all nested resources.

Next steps

For more information on the Azure PowerShell, see Azure PowerShell documentation.

Additional SQL Database PowerShell script samples can be found in the Azure SQL Database PowerShell scripts.