Muokkaa

Jaa

High availability and disaster recovery checklist - Azure SQL Database

  • Artikkeli

Applies to: Azure SQL Database

The 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.

Availability checklist

The following are recommended configurations to maximize availability:

High availability checklist

The following is the recommended configuration to achieve high availability:

  • Enable zone redundancy where it's available for the database or elastic pool to ensure resiliency for zonal failures.

Disaster recovery checklist

Although Azure SQL Database automatically maintains availability, there are instances when even having high availability (zone redundancy) might not guarantee resiliency as the impacting outage spans an entire region. A regional Azure SQL Database outage might require you to initiate disaster recovery.

To best prepare for disaster recovery, follow these recommendations:

  • Enable failover groups for a group of databases.
    • Use the read-write and read-only listener endpoints in your application connection string so applications automatically connect to whichever server and database is the current primary.
    • Set the failover policy to customer managed.
  • Alternatively to failover groups, you can enable active geo-replication to have a readable secondary database in a different Azure region.
  • 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 the 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 a 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) by using the replication_lag_sec column of the sys.dm_geo_replication_link_status dynamic management view (DMV) on the primary database. The DMV shows lag in seconds between the transactions committed on the primary and hardened to the transaction log on the secondary. For example, assume 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.
  • If enabling failover groups or active geo-replication isn't possible, then consider setting the backup storage redundancy option to Geo-redundant backup storage to use the geo-restore capability.
  • Frequently plan and execute disaster recovery drills so you're better prepared in the event of a real outage.

Prepare secondary for an outage

To successfully recover to another data region using either active geo-replication, 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 a server in another region to become the new primary server. If your primary region has a paired region, it's common to use the paired region as your secondary region. By doing this, you typically reduce the latency for replication and geo-restore 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've configured failover groups and use the read-write and read-only listener in application connection strings, no further action is needed - connections are automatically directed to new primary after failover.
  • Identify, and optionally define, the firewall rules that users need 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 server and make it identical on the secondary server.

Related content