Azure SQL Failover Groups: Configure Geo-Failover for Database and MI

An Azure SQL failover group replicates a set of databases (or, for SQL Managed Instance, every user database on the instance) to a secondary server in another region and gives you two DNS listeners that always point at the current primary and secondary. To set one up, you create the secondary logical server or instance, create the failover group with the customer-managed (Manual) policy, change applications to use the <fog-name>.database.windows.net listener, and then prove it works with a planned failover and failback. Microsoft documents a typical RTO of under 60 seconds for this option, with data loss depending on what hadn't replicated yet.

Who this is for and what you will have at the end

This guide is for administrators who run production workloads on Azure SQL Database or Azure SQL Managed Instance and need regional disaster recovery that is faster and more predictable than geo-restore from backups.

At the end you will have:

• A secondary server or instance in another region, with matching security configuration.
• A failover group with a deliberate failover policy.
• Applications connected through the read-write and read-only listeners.
• A tested runbook for planned failover, forced failover and failback.

If you haven't yet set up backup retention and restores, start with Azure SQL Database backups, point-in-time restore, LTR and geo-restore. Failover groups don't replace backups: a bad DELETE is replicated to the secondary just like any other change.

How failover groups work

A failover group is a declarative layer over active geo-replication. Replication is asynchronous, so the secondary can be slightly behind the primary.

Listener endpoints

Both are DNS CNAME records created with the group. The failover group name must be globally unique within .database.windows.net, and it can't be renamed later. For Managed Instance, the listeners can't be reached through the instance's public endpoint.

Failover policies

Microsoft's guidance is clear: use customer managed, test with it, and don't rely on Microsoft-managed failover. Watch the defaults: az sql failover-group create defaults to Manual, but az sql instance-failover-group create defaults to Automatic, so always pass --failover-policy explicitly.

Failover types

• Failover (planned) synchronizes fully before switching roles, so there's no data loss. It needs the primary to be reachable. Use it for DR drills, relocating workloads and failback.
• Forced failover switches roles immediately and can lose recent transactions. Use it when the primary region is unavailable. When the old primary comes back, it reconnects as the new secondary.

Prerequisites

Azure SQL Database

• The primary database or elastic pool already exists.
• A secondary logical server in a different region, ideally the paired region. Failover groups can only be created between servers in the same Microsoft Entra tenant.
• Server logins and firewall settings on the secondary must match the primary.
• For pooled databases, the secondary server needs an elastic pool with exactly the same name and enough free capacity.
• Azure RBAC write access on both servers and all databases in the group. The SQL Server Contributor role has everything needed.

Azure SQL Managed Instance

• The secondary instance must be empty and should match the primary's service tier, compute size and storage size.
• Both instances must share the DNS zone. You set this when you create the secondary, and you can't change it afterwards.
• The two virtual networks must not have overlapping address ranges, including any networks peered with either.
• Connectivity between the subnets, with global virtual network peering recommended. VPN gateways and ExpressRoute also work.
• NSG rules on both subnets allowing inbound and outbound TCP on port 5022 and the range 11000-11999 between the instances. Firewalls or network virtual appliances on the path must allow the same.
• Both instances must use the same update policy.
• An instance can be in only one failover group, and the group always contains all user databases on it.

Step 1: Create the secondary

For SQL Database, create a logical server in the DR region with the same administrator configuration. Then copy security configuration before you need it. If applications use SQL logins mapped to database users, the logins on the secondary must have the same SIDs, or users are orphaned after failover. Run this in master on the primary:

SELECT [name], [sid]
FROM [sys].[sql_logins]
WHERE [type_desc] = 'SQL_Login';

Then create each login on the secondary server's master with the same SID:

CREATE LOGIN [app_contoso]
WITH PASSWORD = '<password>',
SID = 0x01060000000000640000000000000000AABBCCDD;

Don't recreate the server admin's SID on the target. Contained database users and Microsoft Entra authentication avoid this step entirely, because the identity lives in the database or in Entra ID; see Azure SQL Microsoft Entra authentication.

For Managed Instance, create the secondary with the primary's DNS zone. In the portal, on the Additional settings tab of Create Azure SQL Managed Instance, set Use as failover secondary to Yes and select the primary instance. With the CLI, pass the primary instance's resource ID to --dns-zone-partner. This example keeps both instances in the same resource group, because the CLI and the portal can't create a Managed Instance failover group across resource groups:

primary_id=$(az sql mi show -g rg-sqlmi-prod -n sqlmi-contoso-prod --query id -o tsv)
 
az sql mi create \
  --resource-group rg-sqlmi-prod \
  --name sqlmi-contoso-dr \
  --location northeurope \
  --subnet "<secondary-subnet-resource-id>" \
  --admin-user sqladmin \
  --admin-password "<password>" \
  --dns-zone-partner "$primary_id"

In PowerShell the equivalent parameter on New-AzSqlInstance is -DnsZonePartner. Size and tier should match the primary.

Step 2: Create the failover group

Azure SQL Database

In the portal: open the primary logical server, select Failover groups under Data management, then + Add group. Enter the failover group name, choose the secondary server, select Configure database and pick the databases. Select Create.

With the Azure CLI:

az sql failover-group create \
  --name fog-contoso-orders \
  --resource-group rg-sql-prod \
  --server sql-contoso-weu \
  --partner-server sql-contoso-neu \
  --partner-resource-group rg-sql-dr \
  --failover-policy Manual \
  --add-db ordersdb customersdb

With PowerShell:

New-AzSqlDatabaseFailoverGroup -ResourceGroupName "rg-sql-prod" `
  -ServerName "sql-contoso-weu" -PartnerServerName "sql-contoso-neu" `
  -PartnerResourceGroupName "rg-sql-dr" `
  -FailoverGroupName "fog-contoso-orders" -FailoverPolicy Manual
 
Get-AzSqlDatabase -ResourceGroupName "rg-sql-prod" -ServerName "sql-contoso-weu" -DatabaseName "ordersdb" |
  Add-AzSqlDatabaseToFailoverGroup -ResourceGroupName "rg-sql-prod" `
  -ServerName "sql-contoso-weu" -FailoverGroupName "fog-contoso-orders"

When you add a single database, a secondary with the same edition and compute size is created automatically on the partner server. The secondary server must not already have a database with the same name unless it is that database's existing geo-secondary.

To add or remove databases later, use az sql failover-group update --add-db or --remove-db. If a secondary is used only for disaster recovery, you can designate it as a license-free standby replica. The configuration guide lists this as a portal-only option when adding databases, while the current CLI reference also exposes --secondary-type Standby on az sql failover-group create and update.

Azure SQL Managed Instance

In the portal, open the primary instance, select Failover groups under Data management, then Add group. Enter the name, pick the secondary instance, choose Manual as the Read/Write failover policy, and leave Enable failover rights off unless the secondary is DR-only.

With the CLI:

az sql instance-failover-group create \
  --name fog-contoso-mi \
  --resource-group rg-sqlmi-prod \
  --mi sqlmi-contoso-prod \
  --partner-mi sqlmi-contoso-dr \
  --partner-resource-group rg-sqlmi-prod \
  --failover-policy Manual

With PowerShell:

New-AzSqlDatabaseInstanceFailoverGroup -Name "fog-contoso-mi" `
  -Location "westeurope" -ResourceGroupName "rg-sqlmi-prod" `
  -PrimaryManagedInstanceName "sqlmi-contoso-prod" `
  -PartnerRegion "northeurope" -PartnerManagedInstanceName "sqlmi-contoso-dr" `
  -FailoverPolicy Manual -GracePeriodWithDataLossHours 1

If the two instances are in different resource groups or subscriptions, create the group with PowerShell or the REST API; the portal and the CLI don't support that for Managed Instance. Once the group exists, you can manage it with any tool.

Initial seeding

After creation, the group seeds the secondary. Microsoft quotes seeding speeds of up to 500 GB an hour for SQL Database and up to 360 GB an hour for Managed Instance over global peering. For Managed Instance, the status stays Seeding until every database is initialized, you can't fail over during that phase, and creation fails if seeding doesn't complete within the documented limit. Plan large instances accordingly and check that the link between the virtual networks has enough bandwidth.

Step 3: Point applications at the listeners

Replace the server name in connection strings with the read-write listener:

Server=tcp:fog-contoso-orders.database.windows.net,1433;Database=ordersdb;Encrypt=True;

For reporting or other read-only work that tolerates some lag, use the read-only listener and declare read intent:

Server=tcp:fog-contoso-orders.secondary.database.windows.net,1433;Database=ordersdb;ApplicationIntent=ReadOnly;Encrypt=True;

By default the read-only listener does not fail over to the primary if the secondary is down, so read-only sessions wait until the secondary recovers. You can change this with --ro-failover-policy Enabled.

The DNS TTL for both listeners is 30 seconds in SQL Database. Clients must reconnect after failover; make sure your data access layer has retry logic for transient connection errors.

Step 4: Plan for the network and the rest of the application

A failover group fails over the database tier only. Microsoft's guidance is to make every dependent component (web front ends, storage, DNS, identity) redundant in the DR region and fail them over together, otherwise cross-region latency can degrade the application.

Network restrictions need extra care:

• Virtual network service endpoints apply to one region only. Because of this, the Microsoft-managed policy can't be enabled when the servers use virtual network rules. Configure rules separately on each server and deploy front ends in both regions.
• Private Link: put the servers in paired regions, use non-overlapping address spaces, create a private endpoint for each server, and have the secondary's endpoint reuse the same private DNS zone as the primary's.
• Firewall rules: public IP rules must exist on both servers.

The broader landing zone and hybrid network design is covered in the enterprise Azure cloud migration playbook.

Step 5: Test with a planned failover

Run the drill in a maintenance window. Check roles first, fail over to the secondary, verify, then fail back.

az sql failover-group show \
  --name fog-contoso-orders \
  --resource-group rg-sql-prod \
  --server sql-contoso-weu \
  --query replicationRole
 
# Fail over: run against the server that should become primary
az sql failover-group set-primary \
  --name fog-contoso-orders \
  --resource-group rg-sql-dr \
  --server sql-contoso-neu
 
# Fail back
az sql failover-group set-primary \
  --name fog-contoso-orders \
  --resource-group rg-sql-prod \
  --server sql-contoso-weu

In PowerShell, use Switch-AzSqlDatabaseFailoverGroup with -ServerName set to the server that should become primary.

For Managed Instance, set-primary takes the location and resource group of the secondary instance:

az sql instance-failover-group set-primary \
  --name fog-contoso-mi \
  --resource-group rg-sqlmi-prod \
  --location northeurope

In the portal, the Failover button on the failover group page performs a planned failover; confirm the warning that TDS sessions will be disconnected. For Managed Instance, Microsoft notes role switching can take up to five minutes under normal conditions, during which some databases on the new primary are still read-only.

Step 6: Know how to force a failover

In a real outage, when the primary isn't reachable, add --allow-data-loss:

az sql failover-group set-primary \
  --name fog-contoso-orders \
  --resource-group rg-sql-dr \
  --server sql-contoso-neu \
  --allow-data-loss

--try-planned-before-forced-failover attempts a planned failover first and falls back to forced if it fails. For Managed Instance, use az sql instance-failover-group set-primary --allow-data-loss or the REST Force Failover Allow Data Loss operation.

Don't use forced failover for routine drills. For critical transactions, the application can call sp_wait_for_database_copy_sync after commit; it blocks until the transaction is hardened in the secondary's log, at the cost of added latency.

Verification

• az sql failover-group show reports the expected replicationRole on each server, and the listener resolves to the current primary.
• On the primary, query sys.dm_geo_replication_link_status to watch replication lag.
• Application logins work against the secondary after a drill.
• The Activity log of the new primary shows the failover operation. A failover triggered by Microsoft shows Event initiated by as a single hyphen.
• Configure the same long-term retention policy on the secondary as on the primary, because backups are only generated once a database becomes primary. For Managed Instance, change backup redundancy and LTR settings on both instances; they aren't synchronized.

Troubleshooting

"The source database 'Primaryserver.DBName' cannot have higher edition than the target database 'Secondaryserver.DBName'. Upgrade the edition on the target before upgrading the source." You tried to scale the primary to a higher tier first. Scale the secondary up first, then the primary; scale down in the reverse order.

"The operation cannot be performed due to multiple errors" when re-adding a database. Removing a database from a group doesn't stop geo-replication or delete the secondary. Stop replication and delete the secondary copy, then add the database again.

Error 45122, "Create or update Failover Group operation successfully completed; however, some of the databases could not be added to or removed from Failover Group. Provisioning of zone redundant database/pool is not supported for your current request." High availability (zone redundancy) is enabled on the primary and the secondary region doesn't support availability zones. Create the secondary with active geo-replication first, where you can choose the high availability setting, then add it to the group.

Managed Instance roles don't switch. Check peering, NSG rules for 5022 and 11000-11999, and any network virtual appliance in the path. Microsoft advises sending replication traffic directly between the instance subnets rather than through a hub.

Users can't log in after failover. Logins weren't created on the secondary with matching SIDs. For Managed Instance, Agent jobs and logins also need to exist on the secondary.

Database rename fails. Databases in a failover group can't be renamed; remove the database or delete the group first.

Checklist

• Secondary in the paired region, same tier and size, matching logins and firewall rules.
• Failover group created with --failover-policy Manual set explicitly.
• Applications use the listener, with retry logic.
• Network paths and front ends available in both regions.
• LTR and backup redundancy configured on both sides.
• Planned failover and failback tested and timed; forced failover procedure documented.

References

• https://learn.microsoft.com/en-us/azure/azure-sql/database/failover-group-sql-db
• https://learn.microsoft.com/en-us/azure/azure-sql/database/failover-group-configure-sql-db
• https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/failover-group-sql-mi
• https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/failover-group-configure-sql-mi
• https://learn.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-security-configure
• https://learn.microsoft.com/en-us/azure/azure-sql/database/business-continuity-high-availability-disaster-recover-hadr-overview
• https://learn.microsoft.com/en-us/azure/azure-sql/database/long-term-retention-overview
• https://learn.microsoft.com/en-us/cli/azure/sql/failover-group
• https://learn.microsoft.com/en-us/cli/azure/sql/instance-failover-group
• https://learn.microsoft.com/en-us/cli/azure/sql/mi
• https://learn.microsoft.com/en-us/powershell/module/az.sql/new-azsqldatabasefailovergroup