Forced manual fail-over with data loss (2 step)
To force failover with data loss, connect to the SQL Server instance that hosts the target secondary replica and then run the following command:
ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS;
When the previous primary replica recovers, it will also assume the primary role. To ensure that the previous primary replica transitions into a secondary role run the following command on the previous primary replica.
ALTER AVAILABILITY GROUP [ag1] SET (ROLE = SECONDARY);
To manually fail-over without data loss:
1 Make the target secondary replica SYNCHRONOUS_COMMIT.
ALTER AVAILABILITY GROUP [ag1]
MODIFY REPLICA ON N'<node2>'
WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
2 To identify that active transactions are committed to the primary replica 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 REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT to 1.
ALTER AVAILABILITY GROUP [ag1]
SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);
4 Offline the primary replica in preparation for role changes.
ALTER AVAILABILITY GROUP [ag1] OFFLINE
5 Promote the target secondary replica to primary.
ALTER AVAILABILITY GROUP ag1 FORCE_FAILOVER_ALLOW_DATA_LOSS;
6 Update the role of the old primary to SECONDARY, run the following command on the SQL Server instance that hosts the primary replica:
ALTER AVAILABILITY GROUP [ag1] SET (ROLE = SECONDARY);
Other Reference
https://www.mssqltips.com/sqlservertip/3437/manual-sql-server-availability-group-failover/
