Azure SQL Database Change Primary Region for Failover Group

Problem

This article will demonstrate a step-by-step process to change the primary region of a failover group created for Azure SQL databases. If you want to read similar steps for changing the secondary region of the failover group, it's recommended to read this tip: Change secondary region of a failover group for Azure SQL database.

Solution

We had a requirement to change the primary region of the failover group from the US region to the Europe region. There were two Azure SQL databases, mds and mds-prod, hosted on a primary SQL Server, mds-sql-server, in the East US region. A failover group named mds-db-fg was configured for both SQL databases, having its secondary SQL Server mds-sql-server-fgne, hosted in the North Europe region. Our objective is to run our applications from a new primary SQL Server hosted in West Europe and have a failover group in its current secondary SQL Server, mds-sql-server-fgne, as it currently is configured. We will create another SQL Server, mds-sql-server-we, in the West Europe region and make it primary.

The high-level steps to change the primary region of any failover group include:

Validate existing failover group mds-db-fg.
Perform a geo failover from the primary SQL Server mds-sql-server hosted in the East US region to the Secondary SQL Server mds-sql-server-fgne hosted in North Europe.
Create geo-replication for all Azure SQL databases added in the failover group to the new desired region, West Europe, on a newly created SQL Server, mds-sql-server-we.
Remove the failover group, mds-db-fg, configured between the East US and North Europe regions.
Create a new failover group with the same name, mds-db-fg, between the current primary SQL Server, mds-sql-server-fgne, hosted in North Europe and the newly added SQL Server, mds-sql-server-we, hosted in West Europe region.
Failback the failover group to make the newly added region, West Europe, the primary region.
Remove SQL Server, mds-sql-server, which was primary before starting this exercise.

Validate Existing Failover Group

Before moving ahead, let's validate the existing failover group configuration and all databases added to this failover group. You can access the Failover groups tab under Data management in the left pane of the primary SQL Server mds-sql-server overview page in the Azure portal. Failover group details will be available in the right pane. Click the failover group name to open its configuration page and access the Databases within group tab to verify the details, as shown in the below image.

Validate failover group configured for Azure SQL databases

Let's perform a failover to make the current secondary SQL Server, mds-sql-server-fgne, primary.

Perform Geo Failover to Bring Secondary Region as the Primary

Log in to the Azure portal and access the failover group configuration page by accessing the Failover groups tab given under Data management in the left pane of the primary SQL Server, mds-sql-server, hosted in the East US region. Click on Failover to initiate the failover process. This process will cause several minutes of downtime, so plan this activity carefully during off-business hours.

Once failover is complete, notice that the roles of the primary and secondary SQL Server have changed. We can validate in the below screen whether both Azure SQL databases are online from the North Europe region or whether they were online earlier for read-write operations from the East US region.

Perform a failover to make secondary region as primary

The above image shows that the primary SQL Server has become secondary.

Create Geo-Replication

Next, we will configure active geo-replication for all Azure SQL databases added to the failover group. Each database will have two geo-replicas after creating geo-replication. You can read the attached article to learn the step-by-step process to learn how to configure active geo-replication for an Azure SQL database.

Let's start with Azure SQL database mds. Jump to the overview page of this database on the Azure portal. Click the Replicas tab under Data management in the left pane. You will see a geo-replica for this database configured as part of the failover group. We will create additional geo-replicas for this database, mds, in the West Europe region.

Validate all geo replicas configured for databases

Click the + Create replica tab from the right pane (see image above). The form below will appear to fill in details for geo-replication. Keep all the details identical to the primary server except its location and server name. You can verify details that a new server is being created in the West Europe region. Click Review + create.

If a server name doesn't appear in the drop-down menu, click the Create new link to access the new server creation page. The below window will appear to fill in the details for the new secondary server. It might take some time to reflect the newly created SQL Server, mds-sql-server-we, in the server drop-down of the new geo-replication creation page. You may wait some time for it to reflect in the drop-down menu if it is not showing after creating the secondary server.

Once geo-replication is created, you can verify it by accessing the Replicas page. Here you can see two geo-replicas: one is using the failover group, and another is using geo-replication. The newly created replica is highlighted in a dark red rectangle on the screen below.

Similarly, you need to configure active geo-replication for another database, mds-prod. You can see only one replica for this database, which is not replicating to our new target region, West Europe. The same steps from the previous process can be used for geo-replication for Azure SQL database, mds.

Both databases have been configured with geo-replication to the West Europe region. You can validate geo-replicas for another database, mds-prod, in the screen below.

Remove Failover Group

The next phase is to remove the existing failover group, mds-db-fg, which is configured between East US and North Europe regions. Remember, all connections using listener endpoints will start failing after failover group removal. You can note listener endpoint details from the below image.

Click Delete to remove this failover group.

A warning window will pop up confirming the failover group removal and replication state of the databases post-removal. Click Yes to remove the failover group.

You can see the removal process on the screen, as shown in the below image.

Once the failover group is removed, you will return to the Failover groups page and see no failover group in the right pane.

A warning popup window states that data replication between Azure SQL databases will continue flowing. You can verify it by accessing the Replicas page of any database added to the failover group. We can validate it in the image below.

Recreate Failover Group with the Same Name

The failover group between the East US and the North Europe regions has been removed in the above section. Next, we will recreate the failover group between servers hosted in North Europe and newly created servers in West Europe regions.

Click Add group from the right pane of the Failover groups option of the current primary SQL Server, mds-sql-server-fgne. The screen below will appear where you can enter details. After entering the details, click Create to create this failover group. In the screen below, I have added Azure SQL databases to this failover group.

Once the failover group is created, it will reflect in the right pane with other details, as shown below. The failover group is now configured between the North Europe and West Europe regions. Databases and servers hosted in the East US region are no longer part of this failover group.

All connections configured to use listener endpoints to connect to the database will start being successful after failover group creation. You can verify listener endpoints from the image below to ensure it is the same as before.

You can further validate other details by accessing the Databases within group tab (above) to see database details (below).

Perform Geo Failover to the New Primary Region

Our objective was to run Azure SQL databases from the West Europe region. Next, we will perform geo failover to the West Europe region on the newly added SQL Server, mds-sql-server-we, to make Azure SQL databases online and hosted on this server as primary. Click Failover from the above image to initiate the failover process.

We can validate this failover process by accessing Database within group (below). We can see that the primary server is mds-sql-server-we hosted in West Europe, and mds-sql-server-fgne is a secondary server hosted in North Europe.

This step successfully changed the primary region of a failover group configured for Azure SQL databases. Next, we will clean up the database and server hosted in the East US region, the previous primary region.

Remove Previous Primary SQL Server mds-sql-server

The last step of this exercise is to clean up older, not-in-use systems. If you want to use it, you can keep it alive but must stop the data replication that continues running for both databases. It can be verified by accessing the database Replicas page. The image below shows two geo-replicas of Azure SQL database, mds, and one is still showing in the East US region.

We can safely drop the server, mds-sql-server, hosted in the East US region to remove both databases hosted on the server. I removed this server and then verified the database Replicas page to see the number of geo-replicas for this database. Here no replicas are showing in the East US region.

Validate geo replica post EAST US region server removal

Summary

This article explained a step-by-step process to change the primary region of a failover group configured for one or more Azure SQL databases. Changing a failover group's primary or secondary region is the combination of multiple activities that must be performed sequentially to complete this activity successfully. You need to perform failover and failbacks during this activity which will cause downtime for your applications. During this activity, you must also be aware of database inaccessibility using listener endpoints.

Next Steps

Check out these related articles:

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-06-05

Change Primary Region for Azure SQL Database Failover Group