<#

# This is a PowerShell scripts to demonstrate a Failover group

#>

 

# Initialize

$primaryServer = "< The name of the primary server >"

$backupServer = "< DesiredBackupServerName >"

$backupLocation = "< a different location than the (existing) primary Server >"

$failoverGroup = "< desired failover group Name >" # Must be globally unique

$database = " < the name of the database > "

$admin = " < the admin username > "

$password = " < the password for the admin > "

$resourceGroup = " < the name of the resource group from the primary server > "

# Create a backup server in the failover region

New-AzSqlServer -ResourceGroupName $resourceGroup -ServerName $backupServer -Location $backupLocation -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $admin,

$(ConvertTo-SecureString -String $password -AsPlainText -Force))

 

# Create a failover group containing the primary and backup servers

New-AzSqlDatabaseFailoverGroup -ResourceGroupName $resourceGroup -ServerName $primaryServer -PartnerServerName $backupServer -FailoverGroupName $failoverGroup -FailoverPolicy Automatic -GracePeriodWithDataLossHours 1

 

# Add the database to the failover group

Get-AzSqlDatabase -ResourceGroupName $resourceGroup -ServerName $primaryServer -DatabaseName $database | Add-AzSqlDatabaseToFailoverGroup -ResourceGroupName $resourceGroup -ServerName $server -FailoverGroupName $failoverGroup

 

# Verify the failover group properties

Get-AzSqlDatabasseFailoverGroup -ResourceGroupName $resourceGroup -ServerName $primaryServer

# Either the primary or the backup server is fine here

 

# Verify the current role of the backup server (should be "secondary")

(Get-AzSqlDatabaseFailoverGroup -FailoverGroupName $failoverGroup -ResourceGroupName $resourceGroup -ServerName $backupServer).ReplicationRole

 

# Initiate a manual failover

Switch-AzSqlDatabaseFailoverGroup -ResourceGroupName $resourceGroup -ServerName $backupServer -FailoverGroupName $failoverGroup

 

# Verify the current role again (should be "primary")

 

# Initiate a manual failback

Switch-AzSqlDatabaseFailoverGroup -ResourceGroupName $resourceGroup -ServerName $primaryServer -FailoverGroupName $failoverGroup

 

# Verify the current role again (should be back to "secondary")

 

# Failover group created and tested!