How to programmatically add database to failover group

Question

Hello,

We have a solution that has a automated deployment process, where one of the steps is creating a database programtically.

We are using failover groups to ensure that if there is a problem in the main region where the database is, we can fallback to a secondary region.

Currently, we are adding the database manually in the Azure portal (using the method explained at https://learn.microsoft.com/en-us/azure/azure-sql/database/failover-group-add-single-database-tutorial?view=azuresql&tabs=azure-portal) but we would like to perform this programatically using any Azure SDK and in C#.

Can you please give us a step by step guide on how to do this?

Thanks,

Miguel Isidoro

Answer 1

I cannot share with you a way to do it using C#, but you can add a database programmatically to a failover group using PowerShell as shown below. You can execute a PowerShell script from C# as explained here.

$subscriptionId = "<SubscriptionID>"

$resourceGroupName = "<Resource-Group-Name>"

$location = "<Region>"

$adminLogin = "<Admin-Login>"

$password = "<Complex-Password>"

$serverName = "<Primary-Server-Name>"

$databaseName = "<Database-Name>"

$drLocation = "<DR-Region>"

$drServerName = "<Secondary-Server-Name>"

$failoverGroupName = "<Failover-Group-Name>"

# Create a secondary server in the failover region

Write-host "Creating a secondary server in the failover region..."

$drServer = New-AzSqlServer -ResourceGroupName $resourceGroupName `

   -ServerName $drServerName `

   -Location $drLocation `

   -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential `

      -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

$drServer

# Create a failover group between the servers

$failovergroup = Write-host "Creating a failover group between the primary and secondary server..."

New-AzSqlDatabaseFailoverGroup `

   ResourceGroupName $resourceGroupName `

   -ServerName $serverName `

   -PartnerServerName $drServerName  `

   FailoverGroupName $failoverGroupName `

   FailoverPolicy Automatic `

   -GracePeriodWithDataLossHours 2

$failovergroup

# Add the database to the failover group

Write-host "Adding the database to the failover group..."

Get-AzSqlDatabase `

   -ResourceGroupName $resourceGroupName `

   -ServerName $serverName `

   -DatabaseName $databaseName | `

Add-AzSqlDatabaseToFailoverGroup `

   -ResourceGroupName $resourceGroupName `

   -ServerName $serverName `

   -FailoverGroupName $failoverGroupName

Write-host "Successfully added the database to the failover group..."

Answer 2

Hi,

I was able to solve this using C#.

FailoverGroupResource failoverGroup = await sqlServer.GetFailoverGroupAsync(
                region.FailoverGroup
            );
            FailoverGroupPatch failoverGroupPatch = new();
 
            // Add database to failover group
            failoverGroupPatch.Databases.Add(databaseId);
 
            // Update failover group
            await failoverGroup.UpdateAsync(Azure.WaitUntil.Completed, failoverGroupPatch);

Thanks