Script to Perform Azure SQL Premium Failover

  • Article

**

I believe this post is now out of date due to some recent work by the product team, though I have not had the time to try their work yet. Please see this article, it looks to me like there is now a single cmdlet to failover now. My post below was a year before theirs, when nothing existed to do the failover.

**

Azure SQL Database includes a Premium Tier that allows active geo-replication with readable secondaries. I was asked put together a script to automate failover of the primary writable database to the secondary, making it the new primary.

I thought I would put it out here for those to which it may be useful.

Overall Procedure After some research, it looked to me like the basic steps for ‘planned termination’ are:

1. Issue the following command to the primary to stop writes to it (set it read-only): ALTER DATABASE ‘database_name’ SET READ_ONLY

2. Use dm_operation_status DMV on both the primary and the online secondary to determine the status of transactions in progress

3. When the transaction pipe is clear, use Stop-AzureSqlDatabaseCopy to stop replication

4. Once replication is stopped, use Start-AzureSqlDatabaseCopy to restart in the opposite direction

Prerequisites

1. The SQL Client tools must be installed on the machine the script is run from (SQL PowerShell extensions are required).

2. The SA user and password must be the same on the primary and secondary servers. You can be change this by adding an additional parameter to the script to accept the secondary password.

3. You must execute Add-AzureAccount in your PowerShell session with credentials to the Azure subscription that contains the DB servers.

4. You must use Select-AzureSubscription to make the subscription that contains the DB servers the current one.

Script Parameters

failover-AzureSqlDabaseCopy.ps1

-UserName {Your server SA user}

-Password {Your server SA password}

-DatabaseName {Continuous copy DB name}

-NewPrimary {server name that you want to be the primary}

-NewSecondary {server name that you want to be secondary}

Sample command line

PS C:\Scripts> .\failover-AzureSqlDabaseCopy.ps1 -UserName mySA -Password xxxxxxx -DatabaseName LoadTest –NewPrimary r9foajwp82 -NewSecondary i91vi9wndw

The above will fail the LoadTest database on the current primary, i91vi9wndw, to the current secondary, r9foajwp82. The new primary will be r9foajwp82 and it will be placed in continuous copy relationship with i91vi9wndw as secondary.

Script Code

param(
[string] $UserName,
[string] $Password,
[string] $DatabaseName,
[string] $NewPrimary,
[string] $NewSecondary
)

function SetDBReadStatus {
param ($server, $database, $readOnly)
$query = 'alter database [' + $DatabaseName + '] set '
if($readOnly) {
$query += 'read_only'
}
else {
$query += 'read_write'
}

ExecuteQuery $server $database $query

}

function ExecuteQuery {
param($server, $database, $query)
Write-Host $query
$server += '.database.windows.net'
Invoke-SqlCmd -Query $query `
-Database $database `
-EncryptConnection `
-ServerInstance $server `
-Username $UserName `
-Password $Password
}

function Failover {
try {
$roRollbackOK = $false

Write-Host "Setting primary to Read Only"
SetDBReadStatus $NewSecondary 'master' $true
$roRollbackOK = $true
Write-Host "Success - primary is Read Only"

Write-Host "Waiting for pending transactions to clear"
$query = "select count(*) as OpenTX from sys.dm_operation_status where major_resource_id='" + $DatabaseName + "' and state < 2"
do {
$txTest = ExecuteQuery $NewSecondary 'master' $query
$txCount = $txTest.OpenTX

$txTest = ExecuteQuery $NewPrimary 'master' $query
$txCount += $txTest.OpenTX

if($txCount > 0) { Start-Sleep -s 1 }

} while($txCount > 0)

Write-Host "Success - pending transactions cleared"

Write-Host "Stopping continuous copy"
Stop-AzureSqlDatabaseCopy -DatabaseName $DatabaseName `
-ServerName $NewSecondary `
-PartnerServer $NewPrimary
Write-Host "Success - Continuous copy stopped"

Write-Host "Dropping former primary"
$query = 'drop database [' + $DatabaseName + ']'
$txTest = ExecuteQuery $NewSecondary 'master' $query
$roRollbackOK = $false
Write-Host "Success - former primary dropped"

Write-Host "Starting continuous copy, Primary=$NewPrimary, Secondary=$NewSecondary"
Start-AzureSqlDatabaseCopy -DatabaseName $DatabaseName `
-PartnerServer $NewSecondary `
-ServerName $NewPrimary `
-ContinuousCopy
Write-Host "Success - Failover complete"
}
catch {
$err = $true
write-host "Caught an exception:" -ForegroundColor Red
write-host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor Red
write-host "Exception Message: $($_.Exception.Message)" -ForegroundColor Red
}

if($err -and $roRollbackOK) {
Write-Host "Setting primary back to Read/Write due to error"
SetDBReadStatus $NewSecondary 'master' $false
Write-Host "Success - primary is Read/Write"
}
}

$doFailover = $true
$copyStatus = Get-AzureSqlDatabaseCopy -ServerName $NewSecondary -DatabaseName $DatabaseName -PartnerServer $NewPrimary
if($copyStatus -ne $null) {
if($copyStatus.SourceServerName -ne $NewSecondary) {
$doFailover = $false
Write-Host "$NewSecondary is not the current primary"
}

if($copyStatus.DestinationServerName -ne $NewPrimary) {
$doFailover = $false
Write-Host "$NewPrimary is not the current secondary"
}

if($copyStatus.SourceDatabaseName -ne $DatabaseName) {
$doFailover = $false
Write-Host "Database names do not match"
}

if(!$copyStatus.IsContinuous) {
$doFailover = $false
Write-Host "Source, destination, and database name are not in a continuous copy relationship"
}
}
else {
$doFailover = $false
Write-Host "Error retrieving the current continuous copy relationship status"
}

if($doFailover) {
Failover
}