Availability group fail-over

Forced Manual Failover with Data Loss (2-step Process)

To force a failover with potential data loss, connect to the SQL Server instance where the secondary replica is hosted, and execute the following command:

ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS;

Once the original primary replica recovers, it will try to assume the primary role. To ensure the previous primary becomes secondary again, run the command below on that instance:

ALTER AVAILABILITY GROUP [ag1] SET (ROLE = SECONDARY);

Steps to Manually Failover Without Data Loss:

1. First, configure the target secondary replica to use SYNCHRONOUS_COMMIT mode:

ALTER AVAILABILITY GROUP [ag1]
     MODIFY REPLICA ON N''
     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

2. To verify that active transactions are committed to the primary and at least one synchronous secondary replica, run the following query:

SELECT ag.name, drs.database_id, drs.group_id, drs.replica_id,
   drs.synchronization_state_desc, ag.sequence_number
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id;

3. Update the REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT setting to 1:

ALTER AVAILABILITY GROUP [ag1]
     SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);

4. Offline the primary replica in preparation for the role change:

ALTER AVAILABILITY GROUP [ag1] OFFLINE;

5. Promote the target secondary replica to the primary role:

ALTER AVAILABILITY GROUP ag1 FORCE_FAILOVER_ALLOW_DATA_LOSS;

6. Change the role of the previous primary replica to SECONDARY. Run this on the SQL Server instance hosting the previous primary replica:

ALTER AVAILABILITY GROUP [ag1] SET (ROLE = SECONDARY);

For more information, check out this resource:

Check the Writable Copy

Additional reference: Manual SQL Server Availability Group Failover