Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



Azure SQL Database Geo-Replication - Initiate a Failover using PowerShell
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
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