Get AzureSQL failover group by name

I am using the Azure.sql powershell module and my goal is to failover to the secondary server. I want it to be a toggle, and swap the primary and secondary servers regardless of which onces these are.

The Switch-AzSqlDatabaseFailoverGroup cmdlet requires the ResourceGroup and Server Name that will become primary. I don't know these values, but I do know the name of the failover group. I want the script to find the secondary, and use it's servername and resource group.

The Get-AzSqlDatabaseFailoverGroup cmdlet will give me info about the failover group, but it also needs a resource group and server name.

How can I get a failover group with only knowing it's name?

Possible Workaround: I also know the host name of an app service in the same resource group. If I could find a way to get the Resource Group from the app service host name I could use that to also find the database server name in that resourec group. I don't like it, but this work-around would solve my current issue.

Since the PowerShell Az module interacts with the Graph API, the problem comes from the API structure. The API URI to get a failover group looks like:

GET https://management.azure.com/subscriptions/<subscriptionId>/resourceGroups/<resourceGroupName>/providers/Microsoft.Sql/servers/<sqlServerName>/failoverGroups/<failoverGroupName>?api-version=2021-11-01

The Az module already has a subscription context which it assumes after logging in, so it can use this to substitute in the API calls, but not any of the other fields.

Possible Workaround: I also know the host name of an app service in the same resource group. If I could find a way to get the Resource Group from the app service host name I could use that to also find the database server name in that resourec group. I don't like it, but this work-around would solve my current issue.

It's a viable option. I'll write a script which will work with just the failover group name, then I'll show you how we can use this app service resource group to speed up our script.

Iterate All Sql Servers (Method 1)

This script goes through all SQL Servers, checking for an available failover group matching the name provided, and performs a failover.

# Define failover group name
$FailoverGroupName = "Your-Failover-Group"

# Get all SQL servers within the subscription
$servers = Get-AzSqlServer

# Loop through each server

foreach ($server in $servers) {
    # Check if the failover group exists in the current server
    $failoverGroup = Get-AzSqlDatabaseFailoverGroup -ResourceGroupName $server.ResourceGroupName -ServerName $server.ServerName -FailoverGroupName $FailoverGroupName -ErrorAction SilentlyContinue

    # If failover group is found, break the loop
    if ($failoverGroup) {
        if ($failovergroup.ReplicationRole -eq "Primary") {
            break
        }
    }
}

# Check if failover group was found
if ($failoverGroup) {
    # Perform failover using the failover group details
    $secondaryServer = $failoverGroup.PartnerServers[0].ServerName
    $secondaryResourceGroup = $failoverGroup.PartnerServers[0].ResourceGroupName
    
    Switch-AzSqlDatabaseFailoverGroup -ResourceGroupName $secondaryResourceGroup -ServerName $secondaryServer -FailoverGroupName $FailoverGroupName
} else {
    Write-Output "Failover group ('$($FailoverGroupName)'), or primary server for group not found."
}

Use Neighbouring App Service Name (Method 2)

This script gets the resource group name of a known, neighbouring app service plan, to refine the scope of our SQL Server search.

# Define failover group name
$FailoverGroupName = "Your-Failover-Group"

# Define App Service Plan name
$AppServiceHostname = "Your-Hostname"

# Get the Resource Group name of the App Service Plan
$aspResourceGroupName = (Get-AzAppServicePlan -Name $AppServiceHostname).ResourceGroup

# Get all SQL servers within the App Service Resource Group
$servers = Get-AzSqlServer -ResourceGroup $aspResourceGroupName

# Loop through each server

foreach ($server in $servers) {
    # Check if the failover group exists in the current server
    $failoverGroup = Get-AzSqlDatabaseFailoverGroup -ResourceGroupName $server.ResourceGroupName -ServerName $server.ServerName -FailoverGroupName $FailoverGroupName -ErrorAction SilentlyContinue

    # If failover group is found, break the loop
    if ($failoverGroup) {
        if ($failovergroup.ReplicationRole -eq "Primary") {
            break
        }
    }
}

# Check if failover group was found
if ($failoverGroup) {
    # Perform failover using the failover group details
    $secondaryServer = $failoverGroup.PartnerServers[0].ServerName
    $secondaryResourceGroup = $failoverGroup.PartnerServers[0].ResourceGroupName
    
    Switch-AzSqlDatabaseFailoverGroup -ResourceGroupName $secondaryResourceGroup -ServerName $secondaryServer -FailoverGroupName $FailoverGroupName
} else {
    Write-Output "Failover group ('$($FailoverGroupName)'), or primary server for group not found."
}