Azure SQL Failover Group cannot add database after geo-replication was removed. Operation fails with FailoverGroupUnableToPerformGroupOperationOnDatabases

Question

Unable to add an Azure SQL Database to an existing Failover Group after stopping geo-replication. Portal shows success, but database count remains 0/1 and Activity Log reports FailoverGroupUnableToPerformGroupOperationOnDatabases

Answer 1

When you attempt to add a database to an existing Azure SQL Failover Group after stopping geo-replication, you may encounter the error FailoverGroupUnableToPerformGroupOperationOnDatabases. This issue can occur because removing a database from geo-replication does not automatically stop the replication process, and the database may still be considered part of an existing replication relationship.

To resolve this issue, you need to manually stop geo-replication and delete the database from the secondary server before attempting to add it back to the failover group. If you do not perform these steps, you might receive an error indicating that the operation cannot be performed due to multiple errors when trying to add the database back to the failover group.

Make sure to follow these steps:

1. Stop geo-replication for the database.
2. Delete the database from the secondary server if necessary.
3. Attempt to add the database back to the failover group again.

Answer 2

Hi @Anonymous

It seems you're facing an issue when trying to add an Azure SQL Database to an existing Failover Group after stopping geo-replication. The operation fails with FailoverGroupUnableToPerformGroupOperationOnDatabases, and while the portal indicates success, the database count doesn't reflect that.

Here’s what you can do to troubleshoot and resolve this issue:

Troubleshooting Steps:

Check Configuration: Ensure that the failover group configuration settings for the primary and secondary databases match. Misconfiguration or firewall settings can often lead to issues when performing operations in failover groups.

Verify Logins and Firewall Settings:

• Confirm that server logins and firewall settings on both the primary and secondary servers are aligned.
  • The secondary server's login and firewall settings should match those of the primary server.
  Run Diagnostic Queries:

  - Check the geo-replication status by running the following SQL command on the primary server:
  
  ```sql
  SELECT * FROM sys.dm_geo_replication_link_status
  ```
  
     - This will give you insights into the current state of your geo-replication links.
  
     **Delete and Re-add the Database**:
  
        - If the database already exists in a secondary state, consider deleting it from the secondary server.
  
           - After backing up your databases, try adding the database again through the Azure portal:
  
                 - Go to the primary server’s failover group.
  
                       - Add the intended database and allow the seeding to complete.
  
                       **Elastic Pool Considerations**:
  
                          - If your databases are in an elastic pool, ensure the secondary server has an elastic pool with the same name and sufficient capacity. This can sometimes lead to issues if not properly configured.
  

If Issues Continue:

If you've gone through all the steps and still encounter issues, it might be best to open a support case for further assistance.

Follow-Up Questions:

1. Have you verified the firewall settings and server logins for both the primary and secondary servers?
2. Did you run the diagnostic query on sys.dm_geo_replication_link_status? If so, what were the results?
3. Is the database already present on the secondary server, or are there conflicts in elastic pool configurations?
4. Are there any specific error messages in the Azure activity logs that could provide more details?

I hope these steps help you resolve the issue! If you have more information or need further assistance, feel free to ask.

References:

• Troubleshoot adding Azure SQL DBs to failover groups: causes and mitigations
• Configure a failover group for Azure SQL Database
• Check geo-replication status