How to force failover on Clusterless Availability Groups with FORCE_FAILOVER_ALLOW_DATA_LOSS

10 March,2021 by Jack Vamvas

Clusterless Availability Groups are not true HA as there is no underlying cluster - and generally is intended as read only.  I've also seen this set up being used in migration scenarios.

Although not HA - it is still possible to commit a manual failover from a PRIMARY to SECONDARY.  For example in a DR scenario

Prior to commiting a failover identify the set up is a Clusterless Availability Group configuration . When the Clusterless Availability Groups was set up the command will be similar to the following  CREATE AVAILABILITY GROUP  command.

Note:

>> Here we connect to our primary replica (myDemo) and create our Availability Group
>> Note the CLUSTER_TYPE = None and the FAILOVER_MODE = MANUAL 

 
CREATE AVAILABILITY GROUP [MyAGroups]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, FAILURE_CONDITION_LEVEL = 3, HEALTH_CHECK_TIMEOUT = 30000
 , DB_FAILOVER = ON, DTC_SUPPORT = NONE, CLUSTER_TYPE = None) 
FOR DATABASE [myDB]
REPLICA ON N'myDemo' WITH (ENDPOINT_URL = N'TCP://mydemo.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = Read_Only), SESSION_TIMEOUT = 10, SEEDING_MODE = AUTOMATIC),
 N'SERVER1' WITH (ENDPOINT_URL = N'TCP://SERVER1.uk.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 60, SECONDARY_ROLE(ALLOW_CONNECTIONS = Read_Only), SESSION_TIMEOUT = 10, SEEDING_MODE = AUTOMATIC);
GO

 

For post - install Availability replica details , use the sys.availability_replicas DMV.

select replica_server_name, availability_mode_desc,
failover_mode_desc,secondary_role_allow_connections_desc,create_date,
seeding_mode_desc   from sys.availability_replicas

 

To force the failover from the PRIMARY to SECONDARY server , use the FORCE_FAILOVER_ALLOW_DATA_LOSS wsitch on ALTER AVAILABILITY GROUP

ALTER AVAILABILITY GROUP myaggroup FORCE_FAILOVER_ALLOW_DATA_LOSS;
GO

Importantly using the FORCE_FAILOVER_ALLOW_DATA_LOSS will discard transaction log blocks which originated on primary but were unable to transfer to  secondary - upon failover the  secondary has no details therefore can't  replay it - which may lead to data loss when the db comes online.

If you attempt to use the FAILOVER method instead of FORCE_FAILOVER_ALLOW_DATA_LOSS - this warning message will appear

Msg 47122, Level 16, State 1, Line 1
Cannot failover an availability replica for availability group 'myaggroup' since it has CLUSTER_TYPE = NONE. Only force failover is supported in this version of SQL Server.

 

If you attempt to force the failover from the Primary , you'll get the following warning message. Execute the failover  command from the Secondary 

Msg 41122, Level 16, State 11, Line 48
Cannot failover availability group 'myaggroup' to this instance of SQL Server. The local availability replica is already the primary replica of the availability group. To failover this availability group to another instance of SQL Server, run the failover command on that instance of SQL Server. If local instance of SQL Server is intended to host the primary replica of the availability group, then no action is required.

Read more about Always On 

Quick access to SQL Server AlwaysOn Availability cheatsheet


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to force failover on Clusterless Availability Groups with FORCE_FAILOVER_ALLOW_DATA_LOSS


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer