By: Ahmad Yaseen | Comments | Related: > Availability Groups
Problem
SQL Server AlwaysOn Availability Groups are one of the best high availability and disaster recovery solutions which were introduced in SQL Server 2012. Configuring the AlwaysOn Availability Group can be done easily by following a few steps using the New Availability Group wizard. Last week, we faced an issue while creating an AlwaysOn Availability Group. An endpoint encryption algorithm compatibility error prevented us from completing the wizard. What is this issue and how can we overcome this issue?
Solution
Suppose we need to configure a SQL Server AlwaysOn Availability Group between two servers; a primary node and a secondary replica. We go through the steps in the New Availability Group wizard and in the Validation step we get the below error also shown in the screenshot.
We can conclude from this error, that the encryption algorithms used in the replicas endpoints are not compatible with each other. The Primary is using RC4 and the Secondary is using AES. The mirroring endpoint algorithms should be the same between the replicas in order to add these replicas to the AlwaysOn Availability Group and communicate successfully with each other.
SQL Server Encryption Algorithm Options
There are four encryption algorithm options in SQL Server that you can choose from when creating the mirroring endpoints:
- AES - which is the default algorithm in SQL Server 2016.
- RC4 - which is the default algorithm in SQL Server 2014.
- AES RC4 - where the endpoints will negotiate for which algorithm to use, but it will give preference to the AES algorithm.
- RC4 AES - where the endpoints will negotiate for which algorithm to use, but it will give preference to the RC4 algorithm.
Modifying the SQL Server Database Mirroring Endpoint
In order to overcome this issue, you need to make the mirroring endpoints algorithm the same for the two replicas. This can be achieved by changing the encryption algorithm of one of the replicas to be the same as the second one. In our case we will change the algorithm on the secondary replica to RC4.
The easiest way to write the ALTER ENDPOINT statement is to script the endpoint’s creation then modify it as required. The mirroring endpoint can be found under the Server Objects node of the Object explorer using SQL Server Management Studio as follows:
Once the creation script is generated, we need to change the CREATE statement to an ALTER statement since the endpoint already exists. Also, we will change the endpoint algorithm type to RC4 as shown below:
USE [master] GO ALTER 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
Completing SQL Server Availability Group Setup
After applying the script, close the wizard and run it again. You will find that the validation error will not show and you can proceed with the AlwaysOn Availability Group creation process.
Next Steps
- Read more SQL Server AlwaysOn Availability Tips.
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips