Azure SQL Database high availability and disaster recovery checklist

Applies to: Azure SQL Database

Azure SQL Database service automatically ensures all the databases are online, healthy, and constantly strives to achieve the published SLA.

This guide provides a detailed review of proactive steps you can take to maximize availability, ensure recovery, and prepare for Azure outages. This guidance applies to all purchasing models and service tiers of Azure SQL Database.

High availability checklist

The following are recommended configurations that you can implement to maximize availability:

Disaster recovery checklist

Although Azure SQL Database automatically maintains high availability, there are instances when having zone redundancy, retry logic, and maintenance windows might not guarantee availability as the impacting outage spans an entire region. An Azure SQL Database outage may require you to initiate disaster recovery.

The following are recommended configurations to be best prepared for disaster recovery:

  • Enable active geo-replication to have a readable secondary database in a different Azure region.
  • Enable auto-failover groups for a group of databases. Leverage the read-write and read-only listener endpoints that remain unchanged after failover. With auto-failover groups, you can set the failover mode to automatic (default) or manual.
  • Ensure the secondary database is created with the same service tier and service level objective (SLO) as primary database. When scaling up, we recommend that you scale up the geo-secondary first, and then scale up the primary. When scaling down, reverse the order: scale down the primary first, and then scale down the secondary.
  • Disaster recovery by nature is designed to make use of asynchronous replication of data between primary and secondary region. To prioritize data availability over higher commit latency, consider calling the sp_wait_for_database_copy_sync stored procedure immediately after committing the transaction. Calling sp_wait_for_database_copy_sync blocks the calling thread until the last committed transaction has been transmitted and hardened in the transaction log of the secondary database.
  • Monitor lag with respect to Recovery Point Objective (RPO), using the replication_lag_sec column of the sys.dm_geo_replication_link_status dynamic management view (DMV) on the primary database. It shows lag in seconds between the transactions committed on the primary and hardened to the transaction log on the secondary. For example, if the lag is one second, it means that if the primary is impacted by an outage at this moment and a geo-failover is initiated, transactions committed in the last second will be lost.
  • Configure the backup storage redundancy option to Geo-redundant backup storage to leverage geo-restore capability.
  • Frequently plan and execute disaster recovery drills so that you are better prepared in the event of a real outage.

Checklist for preparing secondary for an outage

For success with recovery to another data region using either active geo-replication, auto-failover groups, or geo-restore, you need to prepare a secondary Azure SQL Database logical server in another region. This secondary server can become the new primary server should the need arise. You should also have well-defined steps documented and tested to ensure a smooth recovery. These preparation steps include:

  • For geo-restore, identify the server in another region to become the new primary server. This is generally a server in the paired region for the region in which your database is located. This eliminates the additional traffic cost during the geo-restoring operations.
  • Determine how you are going to redirect users to the new primary server. This could be accomplished by manually changing application connection strings or DNS entries. If you have enabled auto-failover groups and use the read-write and read-only listener in application connection strings, no further action is needed as connections will be automatically directed to new primary.
  • Identify, and optionally define, the firewall rules needed for users to access the new primary database.
  • Identify, and optionally create the logins that must be present in the master database on the new primary server, and ensure these logins have appropriate permissions in the master database, if any. For more information, see Azure SQL Database security after disaster recovery.
  • Identify alert rules that need to be updated to map to the new primary.
  • Document the auditing configuration on the current primary.

Next steps