Have you ever encountered an error while trying to convert database mirroring to the AlwaysOn availability group in SQL Server? If so, you’re not alone. In this article, we will discuss a common error that occurs during this process and provide a solution to fix it.

One of our clients recently faced an issue when attempting to join the availability group. The error message provided by the wizard was not very helpful, so we decided to dig deeper. Upon checking the SQL Server ERRORLOG, we found the following message: “The database Mirroring login attempt failed with error: ‘Connection handshake failed. There is no compatible encryption algorithm. State 22.'”

After further investigation, we discovered that the issue was related to the encryption algorithm used by the endpoints on both the primary and secondary replicas. The primary replica was configured with AES encryption, while the secondary replica was using RC4 encryption. This mismatch in encryption algorithms was causing the failure.

To resolve this issue, we needed to ensure that the ALGORITHM parameter for the endpoints on both replicas matched. We scripted the endpoint configuration for both replicas and compared the values. Here is an example of the endpoint configuration:

Primary Replica:
CREATE ENDPOINT [Mirroring] 
      STATE=STARTED
      AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
      FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

Secondary Replica:
CREATE ENDPOINT [Hadr_endpoint] 
      STATE=STARTED
      AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
      FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

You can also use the following query to check the encryption algorithm on both replicas:

SELECT encryption_algorithm_desc FROM sys.database_mirroring_endpoints

If you run this query, you should see different values for the encryption algorithm on each replica.

To fix the issue, we dropped and recreated the endpoint on the secondary replica using the same script as the primary replica. This ensured that both replicas were using the same encryption algorithm.

In conclusion, when encountering errors during the conversion of database mirroring to AlwaysOn availability groups in SQL Server, it is important to check the encryption algorithm used by the endpoints on both replicas. Ensuring that the ALGORITHM parameter matches on both replicas will resolve the issue and allow for successful data movement within the availability group.