Alwasy on steps to manual failover

Steps to Perform Force Failover for SQL Server AlwaysOn Availability Groups

 The recovery plan should have these three steps:

1. Take into consideration the possibility of data loss and communicate this to the application support team.
2. Perform a forced failover on the secondary replica.
3. Resume data movement.

Step 1 - Check for Potential Data Loss

Execute the code below:

USE master;
GO

SELECT is_failover_ready, *
FROM sys.dm_hadr_database_replica_cluster_states
WHERE replica_id = (SELECT replica_id FROM sys.availability_replicas WHERE replica_server_name = 'yourreplicanamehere')
GO

If is_failover_ready = 1 on a database for a given secondary replica, you can execute the ALTER AVAILABILITY GROUP with the option FORCE_FAILOVER_ALLOW_DATA_LOSS without data loss on this secondary replica. Otherwise, if the value is 0 and if you force the failover to your secondary replica, data loss is possible.

Step 2 - Perform Failover 

After considering the possibility of data loss, the next step is to perform a manual failover to the secondary site. The manual failover transitions the secondary replica to the primary role and suspends the secondary databases. Also note that at this point, data movement from the primary site to secondary site is suspended.

To do a manual failover, you may execute the code below:

USE master;
GO

ALTER AVAILABILITY GROUP 'youragnamehere' FAILOVER
GO

Alternatively, to force the failover with possible data loss, based on your checking for data loss from step 1, you can execute the following code:

USE master;
GO

ALTER AVAILABILITY GROUP 'youragnamehere' FORCE_FAILOVER_ALLOW_DATA_LOSS
GO

The above statements requires ALTER AVAILABILITY permission or CONTROL SERVER permission.

Alternatively, the manual failover can be also done via SQL Server Management Studio. From Object Explorer, expand Availability Groups > right click on the Availability Group to failover and select Failover. This will launch the Failover Wizard window.

After you have considered the possibility of data loss and you have completed the failover with the correct option, go to step 3 from the recovery plan described above.

Step 3 - Resume Data Movement

To resume data movement from the primary to secondary replica, execute the following statement on the secondary replica:

USE master;
GO

ALTER DATABASE 'yourdatabasenamehere' SET HADR RESUME
GO

If for some reason, the database is still in a resolving state, consider the option of setting it offline and then set it back online again. To do this execute the statements below:

USE master;
GO

ALTER DATABASE 'yourdatabasenamehere' SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE 'yourdatabasenamehere' SET ONLINE
RESTORE DATABASE 'yourdatabasenamehere' WITH RECOVERY

The code above may be used also for other scenarios, not only for databases participating in an SQL Server Availability Group.