SQL Server AlwaysOn Automatic Failover

Table of Contents

1. What is Automatic Failover?
2. Requirements for Automatic Failover
3. Environment
4. Verify Automatic Failover Configuration
5. Simulate Automatic Failover
6. Verify New Primary Replica
7. Verify Listener Connectivity
8. Best Practices

1. What is Automatic Failover?

Automatic Failover in SQL Server AlwaysOn Availability Groups allows SQL Server to automatically switch the primary replica to a secondary replica when the primary server becomes unavailable. This ensures high availability and minimizes application downtime.

Automatic failover works only when replicas are configured with Synchronous Commit Mode and Automatic Failover Enabled.

2. Requirements for Automatic Failover

1. Availability Mode must be SYNCHRONOUS COMMIT
2. Failover Mode must be AUTOMATIC
3. Databases must be SYNCHRONIZED
4. Windows Server Failover Cluster (WSFC) must be healthy

3. Environment

4. Verify Automatic Failover Configuration

SELECT 
ar.replica_server_name,
ar.availability_mode_desc,
ar.failover_mode_desc
FROM sys.availability_replicas ar;

-------
select
    ag.name as aag_name,
    ar.replica_server_name,
    d.name as [database_name],
    hars.is_local,
    hars.synchronization_state_desc as synchronization_state,
    hars.synchronization_health_desc as synchronization_health,
    hars.database_state_desc as db_state,
    hars.is_suspended,
    hars.suspend_reason_desc as suspend_reason,
    hars.last_commit_lsn,
    hars.last_commit_time
from sys.dm_hadr_database_replica_states as hars
join sys.availability_replicas as ar
    on hars.replica_id = ar.replica_id
join sys.availability_groups as ag
    on ag.group_id = hars.group_id
join sys.databases as d
    on d.group_database_id = hars.group_database_id
order by aag_name, replica_server_name;

5. Simulate Automatic Failover (DO ONLY IN TEST MACHINES)

To test automatic failover, stop the SQL Server service on the current primary server.

When the primary replica goes offline, the cluster automatically promotes the secondary replica configured for automatic failover.

Stop-Service MSSQLSERVER

6. Verify New Primary Replica

SELECT 
ag.name AS AG_Name,
ar.replica_server_name,
ars.role_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar 
ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars 
ON ar.replica_id = ars.replica_id;

-----

select
    ag.name as aag_name,
    ar.replica_server_name,
    d.name as [database_name],
    hars.is_local,
    hars.synchronization_state_desc as synchronization_state,
    hars.synchronization_health_desc as synchronization_health,
    hars.database_state_desc as db_state,
    hars.is_suspended,
    hars.suspend_reason_desc as suspend_reason,
    hars.last_commit_lsn,
    hars.last_commit_time
from sys.dm_hadr_database_replica_states as hars
join sys.availability_replicas as ar
    on hars.replica_id = ar.replica_id
join sys.availability_groups as ag
    on ag.group_id = hars.group_id
join sys.databases as d
    on d.group_database_id = hars.group_database_id
order by aag_name, replica_server_name;

7. Verify Listener Connectivity

Connect using the Availability Group Listener and verify which server is currently serving connections.

SELECT @@SERVERNAME;
The result should display the new primary replica after automatic failover.

8. Best Practices

• Always configure at least two replicas for automatic failover.
• Monitor synchronization state regularly.
• Use synchronous commit for automatic failover partners.
• Test automatic failover periodically in non-production environments.

Summary:
Automatic Failover in SQL Server AlwaysOn ensures high availability by automatically promoting a synchronized secondary replica to primary when the primary server fails.
Proper configuration of synchronous commit mode and automatic failover settings is
essential for seamless failover with minimal downtime.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/