Microsoft Docs

Azure SQL Database Documentation

High Availability and Disaster Recovery for Azure SQL Database

This tutorial walks you through the concepts and implementation of high availability (HA) and disaster recovery (DR) for Azure SQL Database. Ensuring your data is accessible and protected against various failure scenarios is paramount for business continuity.

Understanding HA and DR in Azure SQL Database

Azure SQL Database offers built-in HA and DR capabilities designed to protect your database from unplanned downtime and data loss. These features are transparent to your application, meaning no application code changes are required to benefit from them.

High Availability (HA)

High Availability ensures that your database remains accessible during planned maintenance and unplanned outages. Azure SQL Database utilizes several technologies to achieve this:

  • Redundancy: Data is replicated across multiple data centers within a region.
  • Automatic Failover: In case of an outage, the system automatically redirects connections to a healthy replica.
  • Service Level Objectives (SLOs): Different service tiers offer varying levels of HA.

Disaster Recovery (DR)

Disaster Recovery focuses on recovering your database in a different geographic region in the event of a regional outage or disaster. Key DR features include:

  • Active Geo-Replication: Asynchronously replicates your database to secondary regions, allowing for quick failover.
  • Auto-Failover Groups: Simplifies management of geo-replication and failover for multiple databases.
  • Geo-restore: Restores a database from a backup taken in a different region.

Configuring Active Geo-Replication

Active Geo-Replication allows you to maintain readable secondary databases in different geographic regions. This is ideal for scenarios where you need to fail over to a different region to keep your applications online during a regional outage.

  1. Navigate to your Azure SQL Database resource in the Azure portal.
  2. In the left-hand menu, under "Data management," select "Replicas."
  3. Click "+ Create SQL database" to configure a new replica.
  4. Select the desired target region and configure server and database settings.
  5. Once created, the primary database will begin replicating to the new secondary database.

Implementing Auto-Failover Groups

Auto-Failover Groups simplify the management of geo-replication and failover for a group of databases. They provide a single point of management for failover and can automatically fail over your databases in a predefined order.

To configure an auto-failover group:

  1. Go to your Azure SQL Server resource in the Azure portal.
  2. Under "Data management," select "Failover groups."
  3. Click "+ Add group."
  4. Provide a name for the failover group and select the primary and secondary regions.
  5. Add the databases you want to include in the group.
  6. Configure the read-write and read-only listener endpoints.
  7. Click "Create."

Important Note

When using Active Geo-Replication or Auto-Failover Groups, ensure your application is designed to handle potential latency introduced by asynchronous replication and to reconnect to the new primary endpoint after a failover.

Geo-Restore and Backups

Azure SQL Database automatically performs backups of your databases. You can restore these backups to any region using the geo-restore capability. This is a simpler DR solution for non-critical workloads or when RPO/RTO requirements are less stringent.

To perform a geo-restore:

  1. Navigate to your Azure SQL Server resource.
  2. Under "Data management," select "Backups."
  3. Choose the database you want to restore.
  4. Select "Restore."
  5. In the restore pane, choose "Other regions" and select your target recovery region.
  6. Configure the new server and database name, then click "Review + create."

Performance Tip

For optimal performance during failover, consider using the same service tier for your primary and secondary databases. Also, ensure network connectivity is configured correctly between regions.

Testing Your DR Strategy

Regularly testing your DR strategy is crucial to ensure it functions as expected. This includes performing manual failovers and verifying that your applications can connect to the new primary database and that data consistency is maintained.

In the Azure portal, you can initiate a manual failover for both Active Geo-Replication and Auto-Failover Groups from their respective configuration pages.

Learn More