info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



Azure SQL Database Geo-Replication - Initiate a Failover using PowerShell
by BF (Principal Consultant; Architecture; Engineering)
2016-09-26








This article shows how to initiate a Geo-Replicated failover for Azure SQL Databases using PowerShell code. It is assuming a two Node
setup with one Node in East US 2 and the other in Central US. It also provides code to check on the Replication Link status and the metadata.
Using the Powershell cmdlet - Set-AzureRmSqlDatabaseSecondary (with the -Failover parameter) you can promote a Secondary Role database to
become the new Primary Role database.




Step 1: Authenticate to Azure Portal

Login-AzureRmAccount


Step 2: Select your Azure Subscription

Select-AzureRmSubscription -SubscriptionName "TST"


Step 3: Execute Powershell


Declare Variables:

#Azure East Region SaaS MSSQL DB:
$RGEast = 'RG_EAST_TST'
$SRV01 = 'azr01'
$DB = 'SALESDB'

#Azure Central Region SaaS MSSQL DB:
$RGCentral= 'RG_CENTRAL_TST'
$SRV02 = 'azr02'


#Get Current Replication Link Status:
Get-AzureRmSqlDatabaseReplicationLink -DatabaseName $DB -ResourceGroupName $RGEast -ServerName $SRV01 -PartnerResourceGroupName $RGCentral -PartnerServerName $SRV02


#Two Node Config: Make East Primary:
Set-AzureRmSqlDatabaseSecondary -ResourceGroupName $RGEast -ServerName $SRV01 -Failover -PartnerResourceGroupName $RGCentral -DatabaseName $DB -Verbose


#Two Node Config: Make Central Primary:
Set-AzureRmSqlDatabaseSecondary -ResourceGroupName $RGCentral -ServerName $SRV02 -Failover -PartnerResourceGroupName $RGEast -DatabaseName $DB -Verbose



Image #1: Get-AzureRmSqlDatabaseReplicationLink





Image #2: Azure Portal - SQL Database - Geo-Replication





Resources:

Overview: SQL Database Active Geo-Replication

Initiate a planned or unplanned failover for Azure SQL Database with PowerShell

How to manage Azure SQL Database security after disaster recovery

Set-AzureRmSqlDatabaseSecondary