You may have encountered the Always-On error 41125 but still cannot bring online the Always-On groups. Check out this article for more information.
The availability replica for availability group 'AvailabilityGroupName'
on this instance of SQL Server cannot become the primary replica because
the WSFC cluster was started in Force Quorum mode. Consider performing a
forced manual failover (with possible data loss). (Microsoft SQL Server,
Error: 41125)
As a database administrator, it's important to have a plan in place for
dealing with unexpected failures in your Always On Availability Groups. One
option is to perform a forced manual failover, which allows you to take
control of the failover process and manually failover to a secondary
replica.
Before you begin, it's important to understand the potential consequences of a forced manual failover. This type of failover will interrupt client connections and may result in the loss of any unsaved data on the primary replica. As a result, it should only be performed as a last resort, and you should always have a good reason for doing so.
To perform a forced manual failover, you'll need to use the ALTER AVAILABILITY GROUP command in SQL Server Management Studio. This command allows you to change the properties of an availability group, including which replica is currently the primary.
To begin the failover process, connect to the primary replica of your availability group and execute the following command:
ALTER AVAILABILITY GROUP
[YourAvailabilityGroup] FAILOVER;
This will initiate the failover process and cause the secondary replica to
become the new primary. Depending on the size of your database and the
amount of data that needs to be synchronized, this process may take a few
minutes to complete.
Once the failover is complete, you can verify the new primary replica by executing the following command:
SELECT *
FROM sys.availability_groups;
This will return a list of all availability groups in the SQL Server instance, along with their current status and the primary replica. If the failover was successful, you should see the secondary replica listed as the primary for your availability group.
In some cases, you may need to perform a forced manual failover even if the secondary replica is not fully synchronized with the primary. In these situations, you can use the FORCE_FAILOVER_ALLOW_DATA_LOSS option, which will allow the failover to proceed even if data may be lost.
To use this option, execute the following command:
ALTER AVAILABILITY GROUP [YourAvailabilityGroup]
FORCE_FAILOVER_ALLOW_DATA_LOSS;
As with a regular forced manual failover, this will interrupt client connections and may result in the loss of unsaved data. It should only be used as a last resort, and you should always have a recent backup available in case data is lost.
Overall, a forced manual failover of an Always On Availability Group can be
a useful tool for dealing with unexpected failures and maintaining high
availability for your databases. However, it should be used carefully, and
only as a last resort when other options have been exhausted.
