Azure SQL Database Geo-Replication

This article explains how to configure and manage active geo-replication for Azure SQL Database. Geo-replication provides disaster recovery capabilities by allowing you to create readable, geographically replicated secondary databases that can be failed over to.

What is Geo-Replication?

Geo-replication allows you to asynchronously replicate your primary Azure SQL Database to another Azure region. This feature is designed for disaster recovery scenarios where a region outage might impact your primary database. By having a readable secondary replica in a different region, you can minimize data loss and application downtime.

Key Concepts

• Primary Database: The source database that is being replicated.
• Secondary Database: A readable replica of the primary database in a different Azure region.
• Replication Link: The logical connection between the primary and secondary databases that manages the replication process.
• Failover: The process of switching the primary role from the current primary database to a secondary database. This can be a planned or unplanned failover.
• Failback: The process of returning the primary role back to the original primary database after a failover.

Supported Scenarios

• Disaster Recovery: Protect against region-wide outages.
• High Availability: While not a replacement for Always On Availability Groups within a region, geo-replication complements HA by offering cross-region protection.
• Geo-Distribution: By placing readable secondaries in various regions, you can improve query performance for users located closer to these secondary regions.

How to Configure Geo-Replication

Prerequisites

• An existing Azure SQL Database.
• An Azure subscription with permissions to create resources in at least two Azure regions.

Steps to Create a Geo-Replicated Secondary

You can configure geo-replication using the Azure portal, Azure CLI, or PowerShell.

Using the Azure Portal:

1. Navigate to your primary Azure SQL Database in the Azure portal.
2. In the database overview pane, select Replicas from the left-hand menu.
3. Click Create replica.
4. Configure the settings for the secondary replica:
   • Region: Select the desired Azure region for your secondary database.
   • Server: Choose an existing Azure SQL server or create a new one in the target region.
   • Compute + storage: Select the desired service tier and compute size. It's recommended to match the primary's performance level.
5. Click Review + create, then Create.

Using Azure CLI:

az sql db replica create --dest-resource-group <dest-resource-group-name> --server <dest-server-name> --name <secondary-db-name> --partner-db <primary-db-resource-id> --edition <edition> --service-objective <service-objective> --region <target-region>
            

Using PowerShell:

New-AzSqlDatabaseSecondary -PartnerDatabaseId <primary-db-resource-id> -ResourceGroupName <dest-resource-group-name> -ServerName <dest-server-name> -SecondaryDatabaseName <secondary-db-name> -Edition <edition> -ServiceObjectiveName <service-objective> -Location <target-region>
            

Managing Geo-Replication

Monitoring Replication Status

You can monitor the replication status of your geo-replicated databases in the Azure portal under the Replicas section of your primary database. Key metrics include:

• Replication latency: The time delay between changes being committed on the primary and applied on the secondary.
• Replication state: Indicates whether replication is healthy, paused, or failed.

Performing a Failover

A failover is a critical operation that switches the primary and secondary roles. There are two types of failovers:

• Planned Failover: Used when you can schedule downtime. The primary database is gracefully shut down, all transactions are committed to the secondary, and then the roles are swapped. This results in minimal data loss.
• Unplanned Failover: Used in disaster recovery scenarios when the primary region is unavailable. This might involve some data loss if transactions were not yet replicated.

To perform a failover:

1. Navigate to your primary Azure SQL Database in the Azure portal.
2. Go to Replicas.
3. Select the secondary database you wish to fail over to.
4. Click Failover.
5. Choose the failover type (Planned or Unplanned) and confirm.

Performing a Failback

After a failover, you may want to move the primary role back to the original primary database. This is known as failback.

1. Navigate to the new primary database (which was the secondary).
2. Go to Replicas.
3. Select the database that is now the secondary.
4. Click Failover and choose Planned failover.

Considerations

• Costs: Geo-replication incurs costs for the secondary database and data transfer.
• Performance: Replication is asynchronous, meaning there will be some latency. The performance of the primary and secondary databases affects replication speed.
• RPO/RTO: Geo-replication aims to provide a low Recovery Point Objective (RPO) and Recovery Time Objective (RTO), but these depend on your configuration and the nature of the outage.

Best Practices

• Choose secondary regions that are geographically distant but still accessible for your users.
• Ensure the secondary database has a comparable or higher performance tier to handle potential failover load.
• Regularly test your failover process to ensure it works as expected.
• Document your disaster recovery plan, including failover and failback procedures.