SQL Serverfailover guide

SELECT
    SERVERPROPERTY('ServerName') AS server_name,
    SERVERPROPERTY('MachineName') AS machine_name,
    SERVERPROPERTY('IsClustered') AS is_clustered,
    SERVERPROPERTY('IsHadrEnabled') AS is_hadr_enabled,
    SERVERPROPERTY('HadrManagerStatus') AS hadr_manager_status;

SELECT
    cluster_name,
    quorum_type_desc,
    quorum_state_desc
FROM sys.dm_hadr_cluster;

SELECT
    NodeName,
    status_description
FROM sys.dm_os_cluster_nodes;

SELECT *
FROM sys.dm_os_cluster_properties;

SELECT
    ag.name AS availability_group_name,
    ag.automated_backup_preference_desc,
    ag.failure_condition_level,
    ag.health_check_timeout,
    ags.primary_replica,
    ags.primary_recovery_health_desc,
    ags.secondary_recovery_health_desc,
    ags.synchronization_health_desc,
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ar.session_timeout,
    ar.backup_priority,
    ar.primary_role_allow_connections_desc,
    ar.secondary_role_allow_connections_desc
FROM sys.availability_groups AS ag
LEFT JOIN sys.dm_hadr_availability_group_states AS ags
    ON ags.group_id = ag.group_id
LEFT JOIN sys.availability_replicas AS ar
    ON ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name;

SELECT
    ag.name AS availability_group_name,
    ar.replica_server_name,
    ars.is_local,
    ars.role_desc,
    ars.operational_state_desc,
    ars.connected_state_desc,
    ars.recovery_health_desc,
    ars.synchronization_health_desc,
    ars.last_connect_error_number,
    ars.last_connect_error_description,
    ars.last_connect_error_timestamp
FROM sys.dm_hadr_availability_replica_states AS ars
JOIN sys.availability_replicas AS ar
    ON ar.replica_id = ars.replica_id
JOIN sys.availability_groups AS ag
    ON ag.group_id = ar.group_id
ORDER BY ag.name, ar.replica_server_name;

SELECT
    ag.name AS availability_group_name,
    ar.replica_server_name,
    COALESCE(adc.database_name, DB_NAME(drs.database_id)) AS database_name,
    drs.is_local,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.database_state_desc,
    drs.is_suspended,
    drs.suspend_reason_desc,
    drs.log_send_queue_size,
    drs.log_send_rate,
    drs.redo_queue_size,
    drs.redo_rate,
    drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar
    ON ar.replica_id = drs.replica_id
JOIN sys.availability_groups AS ag
    ON ag.group_id = ar.group_id
LEFT JOIN sys.availability_databases_cluster AS adc
    ON adc.group_database_id = drs.group_database_id
ORDER BY ag.name, database_name, ar.replica_server_name;

SELECT
    ag.name AS availability_group_name,
    ar.replica_server_name,
    dcs.database_name,
    dcs.is_failover_ready,
    dcs.is_pending_secondary_suspend,
    dcs.is_database_joined,
    dcs.recovery_lsn,
    dcs.truncation_lsn
FROM sys.dm_hadr_database_replica_cluster_states AS dcs
JOIN sys.availability_replicas AS ar
    ON ar.replica_id = dcs.replica_id
JOIN sys.availability_groups AS ag
    ON ag.group_id = ar.group_id
ORDER BY ag.name, dcs.database_name, ar.replica_server_name;

SELECT
    ag.name AS availability_group_name,
    agl.dns_name,
    agl.port,
    agl.is_conformant,
    agl.ip_configuration_string_from_cluster,
    ip.ip_address,
    ip.state_desc AS ip_state_desc,
    tls.type_desc AS listener_type_desc,
    tls.state_desc AS tcp_listener_state_desc,
    tls.start_time
FROM sys.availability_group_listeners AS agl
JOIN sys.availability_groups AS ag
    ON ag.group_id = agl.group_id
LEFT JOIN sys.availability_group_listener_ip_addresses AS ip
    ON ip.listener_id = agl.listener_id
LEFT JOIN sys.dm_tcp_listener_states AS tls
    ON tls.listener_id = agl.listener_id
ORDER BY ag.name, agl.dns_name, ip.ip_address;

SELECT
    ag.name AS availability_group_name,
    adc.database_name,
    ag.automated_backup_preference_desc,
    sys.fn_hadr_backup_is_preferred_replica(adc.database_name) AS is_preferred_backup_replica
FROM sys.availability_groups AS ag
JOIN sys.availability_databases_cluster AS adc
    ON adc.group_id = ag.group_id
ORDER BY ag.name, adc.database_name;

SELECT TOP (120)
    j.name AS job_name,
    j.enabled,
    h.step_id,
    h.step_name,
    h.run_status,
    DATETIMEFROMPARTS(
        h.run_date / 10000,
        (h.run_date % 10000) / 100,
        h.run_date % 100,
        h.run_time / 10000,
        (h.run_time % 10000) / 100,
        h.run_time % 100,
        0
    ) AS run_start_time,
    ((h.run_duration / 10000) * 3600)
      + (((h.run_duration % 10000) / 100) * 60)
      + (h.run_duration % 100) AS run_duration_seconds,
    h.message
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobhistory AS h
    ON h.job_id = j.job_id
WHERE h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -14, SYSDATETIME()), 112))
  AND (
      j.name LIKE N'%backup%'
      OR j.name LIKE N'%log%'
      OR j.name LIKE N'%monitor%'
      OR j.name LIKE N'%alert%'
      OR j.name LIKE N'%maintenance%'
      OR j.name LIKE N'%checkdb%'
      OR h.step_name LIKE N'%backup%'
      OR h.step_name LIKE N'%monitor%'
  )
ORDER BY run_start_time DESC, j.name, h.step_id;

-- Template only. Use during a planned drill after readiness checks pass.
-- Run on the target synchronous secondary replica.

ALTER AVAILABILITY GROUP [YourAvailabilityGroup] FAILOVER;

Forced failover is an incident decision, not a normal test step.

Before considering it:
1. Confirm the primary is unavailable or cannot be trusted.
2. Confirm which secondary has the most recent hardened data.
3. Confirm data-loss exposure with the business owner.
4. Record the decision, target replica, and validation plan.
5. Reconcile or rebuild the old primary after service is restored.

Failover drill checklist

1. Announce the drill window and expected impact.
2. Confirm quorum, replica health, synchronization state, and failover readiness.
3. Confirm backup jobs, monitoring, and application owners are ready.
4. Fail over to the target replica or node.
5. Verify listener, DNS, connection strings, and application workflows.
6. Check SQL Agent jobs, backups, alerts, and monitoring after role change.
7. Decide whether to stay on the new primary or fail back.
8. Record timing, errors, manual steps, and follow-up fixes.