By: Manvendra Singh |Updated: 2023-06-05 |Comments | Related: 1 | 2 | 3 | 4 | > Azure SQL Database
Problem
This article will demonstrate a step-by-step processto 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 failovergroup, 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 ofthe failover group from the US region to the Europe region. There were two AzureSQL databases, mds and mds-prod,hosted on a primary SQL Server, mds-sql-server, in theEast US region. A failover group named mds-db-fgwas configured for both SQL databases, having its secondary SQL Server mds-sql-server-fgne,hosted in the North Europeregion. Our objective is to run our applications from a new primary SQL Server hostedin West Europe and have a failover group in its currentsecondary SQL Server, mds-sql-server-fgne, as it currently isconfigured. Wewill create another SQL Server, mds-sql-server-we, inthe 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-serverhosted in the East US region to the Secondary SQLServer mds-sql-server-fgne hosted in NorthEurope.
- Create geo-replication for all Azure SQL databases added in the failovergroup to the new desired region, West Europe, on anewly created SQL Server, mds-sql-server-we.
- Remove the failover group, mds-db-fg, configuredbetween the East US and North Europeregions.
- 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 Europeregion.
- Failback the failover group to make the newly added region, West Europe, the primary region.
- Remove SQL Server, mds-sql-server, which was primarybefore starting this exercise.
Validate Existing Failover Group
Before moving ahead, let's validate the existingfailover group configuration and all databases added to this failover group. Youcan access the Failover groups tab under Datamanagement in the left pane of the primary SQL Server mds-sql-server overview page in the Azure portal. Failover group detailswill be available in the right pane. Click the failover group name to open its configurationpage and access the Databases within group tab to verifythe details, as shown in the below image.
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 byaccessing the Failover groups tab given under Data managementin the left pane of the primary SQL Server,mds-sql-server, hosted in the East USregion. Click on Failover to initiate the failover process.This process will cause several minutes of downtime, so plan this activity carefullyduring off-business hours.
Once failover is complete, notice that the roles of the primary and secondarySQL Server have changed. We can validate in the below screen whether both AzureSQL databases are online from the North Europe regionor whether they were online earlier for read-write operations from the East US region.
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 addedto 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 learnhow to configure active geo-replication for an Azure SQL database.
Let's start with Azure SQL database mds. Jumpto the overview page of this database on the Azure portal. Click the Replicas tab under Data management inthe left pane. You will see a geo-replica for this database configured as part ofthe failover group. We will create additional geo-replicas for this database,mds, in the West Europe region.
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 servername. 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 theCreate 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 sometime for it to reflect in the drop-down menu if it is not showing after creatingthe 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 usingthe failover group, and another is using geo-replication. The newly created replicais 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 fromthe previous process can be used for geo-replication for Azure SQL database,mds.
Both databases have been configured with geo-replication to the WestEurope 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 NorthEurope regions. Remember, all connections using listener endpointswill start failing after failover group removal. You can note listener endpointdetails from the below image.
Click Delete to remove this failover group.
A warning window will pop up confirming the failover group removal and replicationstate of the databases post-removal. Click Yes to removethe 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 Failovergroups page and see no failover group in the right pane.
A warning popup window states that data replication between Azure SQL databaseswill continue flowing. You can verify it by accessing the Replicaspage of any database added to the failover group. We can validate it in theimage below.
Recreate Failover Group with the Same Name
The failover group between the East US and theNorth 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 Europeregions.
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 whereyou can enter details. After entering the details, click Createto create this failover group. In the screen below, I have added Azure SQL databasesto this failover group.
Once the failover group is created, it will reflect in the right pane with otherdetails, as shown below. The failover group is now configured between theNorth Europe and West Europeregions. Databases and servers hosted in the East US regionare no longer part of this failover group.
All connections configured to use listener endpoints to connect to the databasewill start being successful after failover group creation. You can verify listenerendpoints from the image below to ensure it is the same as before.
You can further validate other details by accessing the Databaseswithin 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 Europeregion. Next, we will perform geo failover to the West Europeregion on the newly added SQL Server, mds-sql-server-we,to make Azure SQL databases online and hosted on this server as primary. ClickFailover from the above image to initiate the failoverprocess.
We can validate this failover process by accessing Database withingroup (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 inNorth Europe.
This step successfully changed the primary region of a failover group configuredfor Azure SQL databases. Next, we will clean up the database and server hosted inthe 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 youwant to use it, you can keep it alive but must stop the data replication that continuesrunning for both databases. It can be verified by accessing the database Replicas page. The image below shows two geo-replicas of AzureSQL database, mds, and one isstill showing in the East US region.
We can safely drop the server, mds-sql-server, hostedin the East US region to remove both databases hostedon 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.
Summary
This article explained a step-by-step process to change the primaryregion of a failover group configured for one or more Azure SQL databases. Changinga failover group's primary or secondary region is the combination of multipleactivities that must be performed sequentially to complete this activity successfully.You need to perform failover and failbacks during this activity which will causedowntime for your applications. During this activity, you must also be awareof database inaccessibility using listener endpoints.
Next Steps
Check out these related articles:
- Add Azure SQL databases to existing auto-failover group
- Remove Azure SQL Databases from a Failover Group and Remove Geo-Replication
- Change Secondary Region of a Failover Group for Azure SQL Database
About the author
Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.
View all my tips
FAQs
How do I change the region of my Azure database? ›
In the Azure portal, search for resource mover. Then, under Services, select Azure Resource Mover. On the Azure Resource Mover Overview pane, select Move across regions.
Which option should you use to geographic failover instances of SQL managed instance? ›For geographic failover of instances of SQL Managed Instance, use Auto-failover groups.
What is the difference between Azure SQL Active Geo-replication and failover groups? ›Auto-failover groups support geo-replication of all databases in the group to only one secondary logical server in a different region. If you need to create multiple Azure SQL Database geo-secondary replicas (in the same or different regions) for the same primary replica, use active geo-replication.
How to configure SQL failover cluster? ›- Create and configure a single-node SQL Server failover cluster instance. ...
- On each node to be added to the SQL Server failover cluster, run Setup with Add Node functionality to add that node.
Using SQL Server Management Studio
Right-click the replica, and click Properties. In the Availability Replica Properties dialog box, use the Failover mode drop list to change the failover mode of this replica.
In the Azure portal, open the relevant resource group. In the resource group page, select the resources that you want to move. Select Move > Move to another region. In Source + destination, select the target region to which you want to move the resources.
How do I change my database location? ›In Object Explorer, right-click on your server and click Properties. In the left panel on that Properties page, click the Database settings tab. In Database default locations, view the current default locations for new data files and new log files.
How do I change the region in Azure function? ›Azure Functions resources are region-specific and can't be moved across regions. You must create a copy of your existing function app resources in the target region, then redeploy your functions code over to the new app.
What is the difference between AlwaysOn and failover cluster in SQL? ›Always On availability groups does not depend on any form of shared storage. However, if you use a SQL Server failover cluster instance (FCI) to host one or more availability replicas, each of those FCIs will require shared storage as per standard SQL Server failover cluster instance installation.
What is the difference between always on availability groups and failover cluster in SQL? ›An SQL AlwaysOn failover cluster instance provides high availability and disaster recovery at the SQL Server level. AlwaysOn Availability Groups (AAG) provide high availability and disaster recovery at SQL database level.
How do I manually failover Azure SQL Database? ›
In the Azure portal, browse to the primary database in the geo-replication partnership. Scroll to Data management, and then select Replicas. In the Geo replicas list, select the database you want to become the new primary, select the ellipsis, and then select Forced failover. Select Yes to begin the failover.
Why would you want to configure geo-replication for an Azure SQL Database? ›Geo-Replication is an Azure SQL database feature that allows you to create a readable secondary database in the same region or cross-region. We can failover to the secondary database in case of an outage for a long time on the primary database server.
What is failover group or geo-replication? ›Auto-failover groups use a geo-replication mechanism to replicate data to another region with additional features not in geo-replication. Auto-failover groups support automatic and manual failover and can also be configured for SQL-managed instances, unlike active geo-replication.
Which is better Hyper V replication or failover cluster? ›Replica is Not a Replacement for Clustering or Automated Failover. The primary difference between HVR and a failover cluster is that HVR always maintains two distinct copies of the same virtual machine whereas clustering uses only one.
What are 3 requirements that must be met in order to build a failover cluster using Windows Server 2016? ›- All nodes on same Windows Server version.
- All nodes using supported hardware.
- All nodes are members of the same Active Directory domain.
In Failover Cluster Manager, expand the cluster name, right-click Roles and then select Configure Role. Follow the steps in the High Availability Wizard to create the clustered role. To verify that the clustered role was created, in the Roles pane, make sure that the role has a status of Running.
How to change the network IP addresses of SQL Server failover cluster instances? ›Using the Failover Cluster Manager Snap-in
On the right pane, under the Server Name category, right-click the SQL Server Instance, and select Properties option to open the Properties dialog box. On the General tab, change the IP address resource. Click OK to close the dialog box.
A forced failover is a form of manual failover that is intended strictly for disaster recovery, when a planned manual failover is not possible. If you force failover to an unsynchronized secondary replica, some data loss is possible.
How do I set up failover settings? ›- Select Configure Failover. Right-click the selected DHCP scope, and select Configure Failover from the context menu. ...
- Specify the partner server. ...
- Create new failover relationship. ...
- Select Finish to complete configuration.
Three forms of failover exist: automatic failover (without data loss), planned manual failover (without data loss), and forced manual failover (with possible data loss), typically called forced failover. Automatic and planned manual failover preserve all your data.
Can a resource group be on different region? ›
Azure resource groups are specific to Azure regions. But resources in a resource group often span multiple regions.
Does Azure resource group region matter? ›Yes. A Resource Group though a logical container will need a region to write it's deployment logs to. So when writing your Azure architectures assume what happens if a region goes down and can't be deployed to. If this happens then are your architecture/deployments resilient?
Why does an Azure resource group have a region? ›(Mostly.) A resource group is just a logical container, and the resources in your resource group can belong to various locations. The answer is pretty straightforward: you may want to have specific locations because of user requirements or maybe that certain Azure resources are only available in certain regions.
How do I move a SQL database to another location? ›In SQL Server, you can move system and user databases by specifying the new file location in the FILENAME clause of the ALTER DATABASE statement. Data, log, and full-text catalog files can be moved in this way. This may be useful in the following situations: Failure recovery.
How do I change the default database location in SQL? ›- Right Click on Server and Select "Properties".
- in the "Server Properties" dialog box, navigate to "Database Settings" tab and data/log files location under "Database default locations" group. ...
- Click on "OK" to apply changes.
MSSQLSERVER\MSSQL\DATA\
Is region the same as location in Azure? ›A Region is a set of datacenters deployed within a latency-defined perimeter and connected through a dedicated regional low-latency network. A Location represents the city or area of the Azure Region. Each Azure Region is paired with another region within the same geography.
What is Azure alternate region? ›Alternate: These regions extend Azure's footprint within a data residency boundary where a recommended region currently exists. Alternate regions help to optimize latency and provide a second region for disaster recovery needs but don't support availability zones.
How do I connect different regions in Azure? ›A second way to connect two VNETs in different locations is by using a VNET-to-VNET connection. A VNET-to-VNET connection is essentially a VPN between the two different Azure locations. The VNET-to-VNET connection is established on a VPN gateway.
How does SQL failover work? ›In the event of a failover, the WSFC service transfers ownership of instance's resources to a designated failover node. The SQL Server instance is then re-started on the failover node, and databases are recovered as usual. At any given moment, only a single node in the cluster can host the FCI and underlying resources.
Is high availability the same as failover? ›
A part of high availability is failover which we define as the ability for client connections to migrate from one server to another in event of server failure so client applications can continue to operate.
What is failover in SQL always on availability group? ›Failover options in the SQL Server Always On Availability Group. Failover is a process to switch the roles of the availability replicas in an AG configuration. Once a failover occurs, the failover target (current secondary) takes the primary role and brings the database online to accept connections.
What is the difference between load balancing and failover clustering? ›Load balancing and failover are two methods of achieving high availability. Load balancing does it by distributing workloads to prevent a single system from getting overloaded, while failover does it by redirecting workload to a backup system when the main system fails.
What is the difference between high availability and failover clustering? ›GitHub Enterprise Server High Availability Configuration (HA) is a primary/secondary failover configuration that provides redundancy while Clustering provides redundancy and scalability by distributing read and write load across multiple nodes.
What is alternative to failover clustering? ›- Veritas InfoScale Availability. 162 comparisons.
- DRBD. 99 comparisons.
- IBM PowerHA System Mirror. 37 comparisons. 4.5. ...
- HPE Serviceguard. 32 comparisons. 4.0.
To initiate a manual failover when the primary database is unavailable and the local standby is available: On the Details page, under Disaster recovery, in the Role field, click Failover.
How to restore Azure SQL Database from one server to another? ›- From Dashboard, select Add > Create SQL Database. ...
- Select Additional settings.
- For Use existing data, select Backup.
- Select a backup from the list of available geo-restore backups.
Open the Azure portal in either Microsoft Edge or Google Chrome. Select your data factory resource to open up its resources on the left hand pane. Select Open Azure Data Factory Studio. The Data Factory Studio can also be accessed directly at adf.azure.com.
What is the difference between geo-replication and active geo-replication? ›Active geo-replication
Up to four secondary are supported in the same or different regions. Geo-replication does not support automatic failover. The failover must be initiated manually by the application or the user.
With multi-region failover, your Production application has a cloned version of its full stack in a secondary failover region. In the event of a failure or substantial impairment in your primary region, you can switch your application immediately to the clone in the secondary region.
What are the two configurations of failover system? ›
Active-Active vs Active-Standby Configurations
The most common high availability (HA) configurations are active-active and active-standby or active-passive.
The failover operation switches production from a primary site to a backup (recovery) site. A failback returns production to the original (or new) primary location after a disaster (or a scheduled event) is resolved.
What is the difference between replication and failover? ›Replication is creating or maintaining multiple copies of something -- generally your database, but possibly more, such an an image of your entire server. Failover is when one system detects that another has failed, and responds by taking over its duties.
What is the difference between always on and failover cluster in SQL? ›Always On availability groups does not depend on any form of shared storage. However, if you use a SQL Server failover cluster instance (FCI) to host one or more availability replicas, each of those FCIs will require shared storage as per standard SQL Server failover cluster instance installation.
What is the difference between failover cluster and mirroring in SQL? ›Failover clusters provide high-availability support for an entire Microsoft SQL Server instance, in contrast to database mirroring, which provides high-availability support for a single database. Database mirroring works between failover clusters and, also, between a failover cluster and a nonclustered host.
How do I manually failover an Azure SQL Database? ›In the Azure portal, browse to the primary database in the geo-replication partnership. Scroll to Data management, and then select Replicas. In the Geo replicas list, select the database you want to become the new primary, select the ellipsis, and then select Forced failover. Select Yes to begin the failover.
What should you configure before you can add the managed instances to the instance failover group? ›- Prerequisites. ...
- Create a resource group and primary managed instance. ...
- Create secondary virtual network. ...
- Create a secondary managed instance. ...
- Create a global virtual network peering. ...
- Create a failover group. ...
- Test failover.
To do a manual failover, navigate to failover groups and click on the name of the failover group. In failover group details page click on failover as shown in the below image. When a planned failover is imitated, the secondary databases are fully synchronized with Azure SQL primary databases before switching the roles.
How to configure AOAG in SQL Server? ›Configure SQL Server
Right click on SQL Server Services and open the Properties dialog box. Navigate to the AlwaysOn High Availability tab and select the Enable AlwaysOn Availability Groups checkbox. Restart the SQL Server Service after making these changes. Complete these steps on all your replicas.
Geo Replication is used for only Azure SQL Databases. Failover groups allow you to manage replication and failover of a group of databases on a server or all databases to another region. It provides automatic failover features. Failover groups support only one secondary server.
How to restore Azure SQL Database to another Azure SQL Server? ›
- From Dashboard, select Add > Create SQL Database. ...
- Select Additional settings.
- For Use existing data, select Backup.
- Select a backup from the list of available geo-restore backups.
Deploying Always On availability groups requires a Windows Server Failover Cluster (WSFC). To be enabled for Always On availability groups, an instance of SQL Server must reside on a WSFC node, and the WSFC and node must be online.
What is always on failover cluster instances? ›SQL Server Always On Failover Cluster Instances (FCIs) use Windows Server Failover Clustering (WSFC) to provide high availability at the server instance level. An FCI is a single instance of SQL Server that is installed across WSFC nodes to provide high availability for the entire installation of SQL Server.
How to configure an Azure internal load balancer as a listener for the availability group? ›- In the Azure portal, open the resource group that contains the SQL Server virtual machines.
- In the resource group, select + Create.
- Search for load balancer. ...
- On the Load Balancer blade, select Create.
- Configure the following parameters for the load balancer.
To force failover (with possible data loss)
Expand the Always On High Availability node and the Availability Groups node. Right-click the availability group to be failed over, and select the Failover command. This launches the Failover Availability Group Wizard.
Failover is a two-phase activity: Failover: The failover that creates and brings up an Azure VM using the selected recovery point. Commit: After failover you verify the VM in Azure: You can then commit the failover to the selected recovery point, or select a different point for the commit.
Which feature allows for the automatic failover of cluster resources? ›Windows Server Failover Clustering (WSFC) -- a feature of Microsoft Windows Server operating system for fault tolerance and high availability (HA) of applications and services -- enables several computers to host a service, and if one has a fault, the remaining computers automatically take over the hosting of the ...
What is AOAG in SQL? ›The Always On availability groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012 (11. x), Always On availability groups maximizes the availability of a set of user databases for an enterprise.
How to set uniqueidentifier in SQL? ›-- If you want to generate a new Guid (uniqueidentifier) in SQL server the you can simply use the NEWID() function. -- This will return a new random uniqueidentifier e.g. You can directly use this with INSERT statement to insert new row in table.
How to add node to failover cluster? ›To add a node to an existing failover cluster instance, click Installation in the left-hand pane. Then, select Add node to a SQL Server failover cluster. The System Configuration Checker will run a discovery operation on your computer. To continue, select OK.