SQL Server AlwaysOn Manual Failover (3-Node Setup) using SSMS (GUI Method)

Table of Contents

0. Pre-requisites
1. Environment
2. Check Synchronization State
3. Identify Failover Target
4. Perform Manual Failover (Run Failover Command on NTICBPSQLSGV02)
5. Verify New Primary
6. Verify Listener Connectivity
7. Validate Database Status and Synchronization Health

0. Pre-requisites

# Manual failover requires Synchronous commit mode.
# Databases must be SYNCHRONIZED.
# No data loss during planned failover.

Note: Forced Failover (Emergency Only). This may cause data loss.

1. Environment

2. Check Synchronization State

select
    ag.name,
    ar.replica_server_name,
    ar.availability_mode_desc as [availability_mode],
    ars.synchronization_health_desc as replica_sync_state,
    rcs.database_name,
    drs.synchronization_state_desc as db_sync_state,
    rcs.is_failover_ready,
    rcs.is_pending_secondary_suspend,
    rcs.is_database_joined
from sys.dm_hadr_database_replica_cluster_states as rcs
join sys.availability_replicas as ar
    on ar.replica_id = rcs.replica_id
join sys.dm_hadr_availability_replica_states as ars
    on ars.replica_id = ar.replica_id
join sys.dm_hadr_database_replica_states as drs
    on drs.group_database_id = rcs.group_database_id
    and drs.replica_id = ar.replica_id
join sys.availability_groups as ag
    on ag.group_id = ar.group_id;

3. Identify Failover Target

Current Primary  : NTICBPSQLSGV01
Failover Target  : NTICBPSQLSGV02

4. Perform Manual Failover (Run Failover Command on NTICBPSQLSGV02)

On NTICBPSQLSGV02:

The key concept is the location where the FAILOVER command is executed.
If you want to promote Node2 as the new Primary replica, you must first log in to server  Node2, open SQL Server Management Studio (SSMS), connect to the SQL instance on Node2, and then initiate the failover.

ALTER AVAILABILITY GROUP … FAILOVER command does not specify the target node, on whichever server you run this command it becomes the new Primary.

T-SQL

ALTER AVAILABILITY GROUP [NTICBPSQLSGA05] FAILOVER;
GO

—- OR —-

GUI Steps:

Right Click Availability Group
→ Click Failover
→ Select the Secondary Replica
→ Perform Manual Failover
→ Finish the Wizard

5. Verify New Primary

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;

6. Verify Listener Connectivity

Connect using the Listener Name and verify the hostname.
It should now route connections to Node2.

7. Validate Database Status and Synchronization Health

select
    ag.name,
    ar.replica_server_name,
    ar.availability_mode_desc as [availability_mode],
    ars.synchronization_health_desc as replica_sync_state,
    rcs.database_name,
    drs.synchronization_state_desc as db_sync_state,
    rcs.is_failover_ready,
    rcs.is_pending_secondary_suspend,
    rcs.is_database_joined
from sys.dm_hadr_database_replica_cluster_states as rcs
join sys.availability_replicas as ar
    on ar.replica_id = rcs.replica_id
join sys.dm_hadr_availability_replica_states as ars
    on ars.replica_id = ar.replica_id
join sys.dm_hadr_database_replica_states as drs
    on drs.group_database_id = rcs.group_database_id
    and drs.replica_id = ar.replica_id
join sys.availability_groups as ag
    on ag.group_id = ar.group_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;

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/