Always On Manual Failover Without Data Loss

Manual failover without data loss

Use this method when the primary replica is available, but you need to temporarily or permanently change which instance hosts the primary replica. To avoid potential data loss, before you issue the manual failover, ensure that the target secondary replica is up to date.

To manually fail over without data loss:

1. Make the current primary and target secondary replica 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: The secondary replica is synchronized when synchronization_state_desc is SYNCHRONIZED.
3. Update REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT to 1.The following script sets REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT to 1 on an availability group named ag1. Before you run the following script, replace ag1 with the name of your availability group:SQLCopyALTER AVAILABILITY GROUP [AGRScale] SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1); This setting ensures that every active transaction is committed to the primary replica and at least one synchronous secondary replica. NoteThis setting is not specific to failover and should be set based on the requirements of the environment.
4. Set the primary replica offline to prepare for the role change:
5. Promote the target secondary replica to primary.
6. Update the role of the old primary to SECONDARY, run the following command on the SQL Server instance that hosts the old primary replica:
8. Resume data movement, run the following command for every database in the availability group on the SQL Server instance that hosts the primary replica.
9. Re-create any listener you created for read-scale purposes and that isn’t managed by a cluster manager. If the original listener points to the old primary, drop it and re-create it to point to the new primary.

ALTER AVAILABILITY GROUP [AGRScale] 
     MODIFY REPLICA ON N'<node2>' 
     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

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; 

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

ALTER AVAILABILITY GROUP [AGRScale] OFFLINE

ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS; 

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

Sources:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/perform-a-planned-manual-failover-of-an-availability-group-sql-server?view=sql-server-ver15