Edit

Azure SQL Database high availability and disaster recovery checklist

  • Article

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:

  • Enable zone redundancy where available for the database or elastic pool to ensure resiliency from a larger set of failures in a region.
  • Incorporate retry logic in the application to handle transient errors.
  • Use maintenance windows to make impactful maintenance events predictable and less disruptive.
  • Test application fault resiliency by manually triggering a failover to see built in High Availability in action.

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.

To be best prepared for disaster recovery, follow these recommendations:

  • Enable active geo-replication to have a readable secondary database in a different Azure region.
  • Enable auto-failover groups for a group of databases. Use 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 that the geo-secondary database is created with the same service tier, compute tier (provisioned or serverless) and compute size (DTUs or vCores) as the primary database.
  • When scaling up, 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, assume that the lag is one second at a point in time. If the primary is impacted by an outage and a geo-failover is initiated at that point in time, transactions committed in the last second will be lost.
  • Configure the backup storage redundancy option to Geo-redundant backup storage to use geo-restore capability.
  • Frequently plan and execute disaster recovery drills so that you're 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 if needed. 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. Using a server in the same region eliminates the extra traffic cost during the geo-restoring operations.
  • Determine how you're going to redirect users to the new primary server. Redirecting users 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 - 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