This PowerShell script example configures an active geo-replication failover group for a single Azure SQL database and fails it over to a 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 primary server
$primaryresourcegroupname = "myPrimaryResourceGroup-$(Get-Random)"
$primarylocation = "eastus"
# Set the resource group name and location for your secondary server
$secondarylocation = "southcentralus"
# Set an admin login and password for your servers
$adminlogin = "ServerAdmin"
$password = "ChangeYourAdminPassword1"
# Set failover group name - the failover group name has to be unique in the system
$failovergroupname = "failovergroupname-$(Get-Random)"
# Set server names - the logical server names have to be unique in the system
$primaryservername = "primary-server-$(Get-Random)"
$secondaryservername = "secondary-server-$(Get-Random)"
# The sample database name
$databasename = "mySampleDatabase"
# The ip address range 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"
# Create new resource group
$primaryresourcegroup = New-AzureRmResourceGroup -Name $primaryresourcegroupname -Location $primarylocation
$primaryresourcegroup
# 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))
$primaryserver
$secondaryserver = New-AzureRmSqlServer -ResourceGroupName $primaryresourcegroupname `
-ServerName $secondaryservername `
-Location $secondarylocation `
-SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
$secondaryserver
# Create a server firewall rule that allows access from the specified IP range
$primaryserverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $primaryresourcegroupname `
-ServerName $primaryservername `
-FirewallRuleName "AllowedIPs" -StartIpAddress $primarystartip -EndIpAddress $primaryendip
$primaryserverfirewallrule
$secondaryserverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $primaryresourcegroupname `
-ServerName $secondaryservername `
-FirewallRuleName "AllowedIPs" -StartIpAddress $secondarystartip -EndIpAddress $secondaryendip
$secondaryserverfirewallrule
# Create a blank database with S0 performance level on the primary server
$database = New-AzureRmSqlDatabase -ResourceGroupName $primaryresourcegroupname `
-ServerName $primaryservername `
-DatabaseName $databasename -RequestedServiceObjectiveName "S0"
$database
# Create failover group
$failovergroup = New-AzureRMSqlDatabaseFailoverGroup `
–ResourceGroupName $primaryresourcegroupname `
-ServerName $primaryservername `
-PartnerServerName $secondaryservername `
–FailoverGroupName $failovergroupname `
–FailoverPolicy Automatic `
-GracePeriodWithDataLossHours 2
$failovergroup
# Add database to failover group
$failovergroup = Get-AzureRmSqlDatabase `
-ResourceGroupName $primaryresourcegroupname `
-ServerName $primaryservername `
-DatabaseName $databasename | `
Add-AzureRmSqlDatabaseToFailoverGroup `
-ResourceGroupName $primaryresourcegroupname ` `
-ServerName $primaryservername `
-FailoverGroupName $failovergroupname
$failovergroup
# Initiate a planned failover
Switch-AzureRMSqlDatabaseFailoverGroup `
-ResourceGroupName $primaryresourcegroupname `
-ServerName $secondaryservername `
-FailoverGroupName $failovergroupname
# Monitor Geo-Replication config and health after failover
Get-AzureRMSqlDatabaseFailoverGroup `
-ResourceGroupName $primaryresourcegroupname `
-ServerName $primaryservername
Get-AzureRMSqlDatabaseFailoverGroup `
-ResourceGroupName $primaryresourcegroupname `
-ServerName $secondaryservername
# Remove the replication link after the failover
Remove-AzureRmSqlDatabaseFailoverGroup `
-ResourceGroupName $primaryresourcegroupname `
-ServerName $secondaryservername `
-FailoverGroupName $failovergroupname
# Clean up deployment
# Remove-AzureRmResourceGroup -ResourceGroupName $primaryresourcegroupname
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. |
| 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 to initiate failover. |
| Get-AzureRmSqlDatabaseReplicationLink | Gets the geo-replication links between an Azure SQL Database and a resource group or SQL Server. |
| Remove-AzureRmSqlDatabaseSecondary | Terminates data replication between a SQL Database and the specified secondary database. |
| 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.


