Problem
Sometimes there is a need to remove an Azure SQL database from a failover group. Today, I will explain how to remove Azure SQL databases from a failover group configured as a business continuity solution. If you are unfamiliar with configuring Azure SQL Auto Failover Groups, I recommend reading this Configure Auto-Failover Group for Azure SQL Database and Azure SQL Managed Instances for SQL Server.
Solution
Business continuity is a capability through which we ensure our systems can keep functioning with minor impact in case of a disaster or unplanned disruption. Auto failover groups is one of the business continuity solutions offered by Azure SQL databases.
Auto failover groups transfer data from the primary SQL Server to a secondary SQL Server hosted in another region with automatic and manual mode failover capabilities. We can add one or more SQL databases to the failover group and fail them over together in a single occurrence. Databases can be added or removed anytime for the failover group. I have explained the step-by-step process to add Azure SQL databases to a failover group in this article Add Azure SQL databases to existing auto-failover group. Today I will demonstrate how to remove a SQL database from a failover group.
Remove SQL Database from Azure Failover Group
We know that adding databases to failover groups increases the cost of Azure billing. You identified some of the databases are not critical and want them to be removed from the failover group to save money, but you want these databases to remain online as a standalone entity and not part of a failover group.
Let’s start by understanding the process and performing the steps to remove databases from the failover group.
There are three main phases involved in removing any Azure SQL database from a failover group:
- Preparation
- Remove the database from the failover group
- Stop data replication, i.e., geo-replication
Preparation
This phase is about doing pre-work, which is mandatory for removing SQL databases from a failover group. The first step towards removing the database from the failover group is to identify the database and understand the requirement. Some considerations include the following:
- Remove the database from the failover group only and let the database continue with data replication
- Stop the data replication for that database post removing it from the failover group and run it as a standalone database
- Decommission the database
Once you have identified your target database and your needs, check the failover group associated with the specified database.
We want to remove Azure SQL database mds-fg from failover group mds-db-fg and keep it running as the standalone database on its primary server mds-sql-server hosted in East US.
Log in to the Azure portal and access the SQL Server dashboard page on which the identified database is hosted. Click on Failover groups under Data management in the left pane to see all failover groups in the right pane. Our target failover group mds-db-fg is shown.
You can verify other details like the number of databases added to this failover group, their names, roles, servers, and the host regions by accessing the failover group configuration page, as shown in the image below. We can see that our identified target database, mds-fg, is in the failover group.
Next, secure backup copies in case the database is decommissioned from your environment. Make sure to follow the retention policy to secure backups of this database for any future uses.
Remove the Database from the Failover Group
Now, we will remove this database from the failover group. Click on the Remove databases tab from the above screen to initiate the database removal process from the failover group. The Remove databases popup window will appear to select the specific database that needs to be removed from the failover group. Click the checkbox next to the target database to choose.
I have clicked on the Azure SQL database mds-fg, as shown in the below image. Click Select to proceed to the next step.
The mds-fg database is shown under the Databases selected for removal tab. Click Save to start the removal process.
A popup window will appear stating that the database will be removed from the failover group, but data replication will continue. Click Yes to proceed.
Once the Azure SQL database is removed, the following notification will appear.
Click OK to close the popup window, followed by the failover configuration window. If you access the Databases within group tab without refreshing or closing the failover group configuration page, the removed database, mds-fg, will still be visible, as shown in the below image. Closing the failover group window or refreshing the page will show the changes.
Our target database, mds-fg, has been removed from the failover group, mds-db-fg. Next, we must validate whether the target database was removed from the failover group. You can access the Failover groups tab in the left pane of the primary or secondary SQL Server to access failover group details in the right pane, as shown in the below image. In the last column of the screenshot below, the database count shows only 2 databases of the 3 eligible databases. Earlier, it showed 3/3 in the database count.
Let’s validate further to check all databases added in this failover group by clicking on this failover group.
You can further access the Databases within group tab from the failover group configuration page to see all databases within the failover group. We can see that only 2 databases (mds and mds-prod) are there, while our target database, mds-fg, is not.
This has validated that our target Azure SQL database has been successfully removed from this failover group.
Now, we can validate the failover group functionality by testing failover to ensure everything is fine after removing the database from this failover group. Click on the Failover tab on the failover group mds-db-fg configuration page, as shown in the below image. Click Yes when the warning appears.
Once failover is completed, you can verify its databases and their SQL Server roles. We can see now that both databases hosted on SQL Server mds-sql-server-fg are online for read-and-write transactions from the North Europe region, which was the secondary server.
When you close the above failover group configuration page, return to the failover group dashboard where primary and secondary server details have changed post-failover.
If you want to failback to run your databases from the primary SQL Server mds-sql-server, you can do it by performing another failover as we did for the above test. I did failback and made the East US region primary.
Stop Geo-Replication Between Azure SQL Databases
As mentioned above, data replication will continue for any database removed from the failover group. We have removed the Azure SQL database mds-fg from the failover group mds-db-fg in the above sections. When you check this database on both primary and secondary SQL servers, you will find it there, as shown in the image below.
Now, we can verify whether replication is still configured for this database after removing it from the failover group. Click on this database from the above screen, or access the overview page of the Azure SQL database mds-fg and click on the Replicas tab under Data management in the left pane to see its data replication status in the right pane. As you can see, geo-replication is there, with a failover policy set as None. Other details can also be verified on this page.
If you want to keep replication running, leave as is. However, if you want to stop geo-replication and keep your database running as a standalone, you need to remove this database from the geo-replication configuration.
Click on the three dots for geo replicas, hosted on the mds-sql-server-fg server. Click the Stop replication option from the drop-down menu, as seen below.
A popup window will appear stating that the replication relationship between both databases will be removed. Click Yes to proceed.
A request will be submitted to stop the replication between both Azure SQL databases. You will be notified once replication is removed. Click Refresh on this page to reflect the changes.
Once replication is removed, details for geo-replication disappear from the right pane, as seen below. It means data replication has been stopped between both databases. Now, each database is working as a standalone database on its respective SQL Servers.
You can see in the image below whether I have accessed both SQL Servers from each region, showing that both databases are online. If you want to run this Azure SQL database mds-fg as a standalone database from only one server, drop this database hosted in the other region. If you don’t need this database at all, you can secure its backup copies for future use and decommission both.
Next Steps
This article explored how to remove the Azure SQL database from the failover group. We first removed the database from the failover group. Then we either left it running in a geo-replication relationship or stopped the data replication to run it as a standalone database.
Check out these related articles:
