Tutorial: Configure active geo-replication and failover (Azure SQL Database)

Applies to: Azure SQL Database

This article shows you how to configure active geo-replication for Azure SQL Database using the Azure portal or Azure CLI and to initiate failover.

For best practices using auto-failover groups, see Auto-failover groups with Azure SQL Database and Auto-failover groups with Azure SQL Managed Instance.

Prerequisites

Portal

To configure active geo-replication by using the Azure portal, you need the following resource:

• A database in Azure SQL Database: The primary database that you want to replicate to a different geographical region.

Note

When using Azure portal, you can only create a secondary database within the same subscription as the primary. If a secondary database is required to be in a different subscription, use Create Database REST API or ALTER DATABASE Transact-SQL API.

Azure CLI

To configure active geo-replication, you need a database in Azure SQL Database. It's the primary database that you want to replicate to a different geographical region.

Prepare your environment for the Azure CLI.

• Use the Bash environment in Azure Cloud Shell. For more information, see Quickstart for Bash in Azure Cloud Shell.

  

• If you prefer to run CLI reference commands locally, install the Azure CLI. If you're running on Windows or macOS, consider running Azure CLI in a Docker container. For more information, see How to run the Azure CLI in a Docker container.

  • If you're using a local installation, sign in to the Azure CLI by using the az login command. To finish the authentication process, follow the steps displayed in your terminal. For other sign-in options, see Sign in with the Azure CLI.

  • When you're prompted, install the Azure CLI extension on first use. For more information about extensions, see Use extensions with the Azure CLI.

  • Run az version to find the version and dependent libraries that are installed. To upgrade to the latest version, run az upgrade.

Add a secondary database

The following steps create a new secondary database in a geo-replication partnership.

To add a secondary database, you must be the subscription owner or co-owner.

The secondary database has the same name as the primary database and has, by default, the same service tier and compute size. The secondary database can be a single database or a pooled database. For more information, see DTU-based purchasing model and vCore-based purchasing model. After the secondary is created and seeded, data begins replicating from the primary database to the new secondary database.

Note

If the partner database already exists, (for example, as a result of terminating a previous geo-replication relationship) the command fails.

Portal

1. In the Azure portal, browse to the database that you want to set up for geo-replication.

2. On the SQL Database page, select your database, scroll to Data management, select Replicas, and then select Create replica.

   

3. Select or create the server for the secondary database, and configure the Compute + storage options if necessary. You can select any region for your secondary server, but we recommend the paired region.

   

   Optionally, you can add a secondary database to an elastic pool. To create the secondary database in a pool, select Yes next to Want to use SQL elastic pool? and select a pool on the target server. A pool must already exist on the target server. This workflow doesn't create a pool.

4. Click Review + create, review the information, and then click Create.

5. The secondary database is created and the deployment process begins.

   

6. When the deployment is complete, the secondary database displays its status.

   

7. Return to the primary database page, and then select Replicas. Your secondary database is listed under Geo replicas.

   

Azure CLI

Select the database you want to set up for geo-replication. You'll need the following information:

• Your original Azure SQL database name.
• The Azure SQL server name.
• Your resource group name.
• The name of the server to create the new replica in.

Note

The secondary database must have the same service tier as the primary.

You can select any region for your secondary server, but we recommend the paired region.

Run the az sql db replica create command.

az sql db replica create --resource-group ContosoHotel --server contosoeast --name guestlist --partner-server contosowest --family Gen5 --capacity 2 --secondary-type Geo

Optionally, you can add a secondary database to an elastic pool. To create the secondary database in a pool, use the --elastic-pool parameter. A pool must already exist on the target server. This workflow doesn't create a pool.

The secondary database is created and the deployment process begins.

When the deployment is complete, you can check the status of the secondary database by running the az sql db replica list-links command:

az sql db replica list-links --name guestlist --resource-group ContosoHotel --server contosowest

Initiate a failover

The secondary database can be switched to become the primary.

Portal

1. In the Azure portal, browse to the primary database in the geo-replication partnership.

2. Scroll to Data management, and then select Replicas.

3. In the Geo replicas list, select the database you want to become the new primary, select the ellipsis, and then select Forced failover.

   

4. Select Yes to begin the failover.

Azure CLI

Run the az sql db replica set-primary command.

az sql db replica set-primary --name guestlist --resource-group ContosoHotel --server contosowest

The command immediately switches the secondary database into the primary role. This process normally should complete within 30 seconds or less.

There's a short period during which both databases are unavailable, on the order of 0 to 25 seconds, while the roles are switched. If the primary database has multiple secondary databases, the command automatically reconfigures the other secondaries to connect to the new primary. The entire operation should take less than a minute to complete under normal circumstances.

Note

This command is designed for quick recovery of the database in case of an outage. It triggers failover without data synchronization, or forced failover. If the primary is online and committing transactions when the command is issued some data loss may occur.

Remove secondary database

This operation permanently stops the replication to the secondary database, and changes the role of the secondary to a regular read-write database. If the connectivity to the secondary database is broken, the command succeeds but the secondary doesn't become read-write until after connectivity is restored.

Portal

1. In the Azure portal, browse to the primary database in the geo-replication partnership.
2. Select Replicas.
3. In the Geo replicas list, select the database you want to remove from the geo-replication partnership, select the ellipsis, and then select Stop replication.
4. A confirmation window opens. Click Yes to remove the database from the geo-replication partnership. (Set it to a read-write database not part of any replication.)

Azure CLI

Run the az sql db replica delete-link command.

az sql db replica delete-link --name guestlist --resource-group ContosoHotel --server contosoeast --partner-server contosowest

Confirm that you want to perform the operation.

Next steps

• To learn more about active geo-replication, see active geo-replication.
• To learn about auto-failover groups, see Auto-failover groups
• For a business continuity overview and scenarios, see Business continuity overview.