Error 40926: The operation cannot be performed because the geo-replication link is part of a Failover Group. You must remove the database from the group in order to individually terminate or failover.
Came across with one of the Azure SQL Database scenario where, couldn’t perform a manual failover for Active Geo-Replication from the Portal.
Notification appears in the Azure portal, as well as in the Activity Logs, with status ‘started’ but it doesn’t perform the failover request.
Getting an error, the operation cannot be performed because the geo-replication link is part of a Failover Group. You must remove the database from the group in order to individually terminate or failover.
Checked the Activity Logs for both primary/secondary db but not found any failed operation. We could only see operation accepted/started, but no completion notification and the failover doesn’t happen.
We tried to trigger the failover from PowerShell using below command
$database = Get-AzSqlDatabase -DatabaseName <dbname> -ResourceGroupName <name> -ServerName <name>
$database | Set-AzSqlDatabaseSecondary -PartnerResourceGroupName <name> -Failover -AllowDataLoss
which eventually ended up with error: 40926: The operation cannot be performed because the geo-replication link is part of a Failover Group. You must remove the database from the group in order to individually terminate or failover.
Tried to remove replication link from PowerShell, which also failed with error:Remove-AzSqlDatabaseSecondary: 40926: The operation cannot be performed because the geo-replication link is part of a Failover Group. You must remove the database from the group in order to individually terminate or failover.
We tried to stop replication from Azure Portal for the current active geo-replication in order to remove the replication link, which also failed with same error:
Stop replication encountered an error for serverName: <name>, databaseName: <name>
ErrorCode: 400 ErrorMessage: The operation cannot be performed because the geo-replication link is part of a Failover Group. You must remove the database from the group in order to individually terminate or failover.
We deleted the secondary db and re-created the active geo-replication. We have then retried a failover, which ended up with the same error:
This seems to be by design.
The database is inside a failover group, which means that a unique database, by itself cannot switch role or perform a geo-failover. If this is performed then the failover group would be split with some databases in one geo location and others in another geo-location. So, if a database is inside a failover group, it cannot be manually geo-restored/geo-failover/switch primary. Only the entire group can be failover at once.
If you need to failover that database, you have two options:
· Remove the database from the failover group and then perform the manual geo failover.
· Failover the entire group
This is documented here: Configure a failover group — Azure SQL Database & SQL Managed Instance | Microsoft Docs
Please make sure you don’t have both Geo-Replication and Failover Group configured for the same databases, as you will run into the above error. Having the database inside a Failover Group it will be geo-replicated. All databases inside FG are geo-replicated.