Guide for Performing SQL Server AlwaysOn Availability Groups Force Failover:

1. Assess Potential Data Loss:

- Acknowledge the possibility of data loss and communicate this to the application support team.

- Check for potential data loss in the secondary replica using the SQL Server DMV dm_hadr_database_replica_cluster_states.

- Execute the code below to verify potential data loss:

USE master;

GO

SELECT is_failover_ready, *

FROM sys.dm_hadr_database_replica_cluster_states;

GO

```

- If is_failover_ready = 1, execute ALTER AVAILABILITY GROUP with FORCE_FAILOVER_ALLOW_DATA_LOSS to perform failover without data loss. If it's 0, data loss might occur upon failover.

2. Execute Failover:

- Perform a manual failover to the secondary site using SQL code or SQL Server Management Studio.

- SQL code for failover:

USE master; GO

ALTER AVAILABILITY GROUP 'agname' FAILOVER

GO

- Optionally, execute the code for forced failover with possible data loss:

USE master;

GO

ALTER AVAILABILITY GROUP 'agname' FORCE_FAILOVER_ALLOW_DATA_LOSS

GO

- Ensure proper permissions (ALTER AVAILABILITY or CONTROL SERVER) for executing the statements.

3. Resume Data Movement:

- After completing failover, resume data movement from primary to secondary replica.

- Execute the following statement on the secondary replica:

USE master;

GO

ALTER DATABASE 'dbname' SET HADR RESUME

GO

- If the database remains in a resolving state, consider setting it offline and then online again with the code:

USE master;

GO

ALTER DATABASE 'dbname' SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE 'dbname' SET ONLINE

RESTORE DATABASE 'dbname' WITH RECOVERY

4. Verification:

- Check the Availability Group dashboard in SQL Server Management Studio.

- Ensure all servers are green, primary and secondary replicas have the correct roles, and databases show as "Synchronized" on the primary and "Synchronizing" on the secondary replica.