DBAtlas · Database operations atlas

Availability Group failover

SQL ServerConfiguration

Command

-- Run on the TARGET (secondary) REPLICA - planned, no data loss (synchronous):
ALTER AVAILABILITY GROUP [AG_Customer] FAILOVER;

-- Forced (asynchronous replica / primary unreachable, DATA LOSS risk):
-- ALTER AVAILABILITY GROUP [AG_Customer] FORCE_FAILOVER_ALLOW_DATA_LOSS;

-- Check status:
SELECT ag.name, ar.replica_server_name,
       rs.role_desc, rs.synchronization_health_desc, ar.availability_mode_desc
FROM   sys.availability_groups ag
  JOIN sys.availability_replicas ar ON ar.group_id = ag.group_id
  JOIN sys.dm_hadr_availability_replica_states rs ON rs.replica_id = ar.replica_id;

What it does

Moves the Availability Group primary role to another replica; a planned failover to a synchronous replica happens without data loss.

When to use it

Role transfer for maintenance/patching, failover when the primary node fails.

Caution

The normal FAILOVER command is run on the SECONDARY replica that will take over. FORCE_FAILOVER_ALLOW_DATA_LOSS is used only when the primary is unreachable or on an asynchronous replica and can CAUSE DATA LOSS; be very careful if synchronization is not healthy.

Open in DBAtlas →

Related