Endpoints for Mirroring and AGs in SQL Server 2016

I migrated a customer to SQL Server 2016 last weekend (YAY!) and ran into an interesting issue. The original environment was SQL Server 2012 on server A. The new environment, running SQL Server 2016, is a three-node Availability Group with servers B, C, and D. I had already set up the AG with a test database in the new environment, with B as the primary and C and D as replicas. To upgrade with little downtime, I mirrored from server A to server B, and that’s where I ran into this error:

Alter failed for Database ‘AdminSQLskills’. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The server network address “TCP://avengers.com:5022” can not be reached or does not next.
Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

[You can see the image of the error in this StackOverflow post, which is also where I found the solution.]

I verified the following:

1. The databases on server B had been restored with NORECOVERY.
2. The accounts I used had the same permissions on both instances.
3. The endpoints existed.
4. Encryption was enabled for both endpoints.

Then I found my issue. The endpoints had different encryption methods.

For SQL Server 2014 and earlier, the endpoints use RC4 encryption by default. Starting in SQL Server 2016, the end points use AES encryption by default (see CREATE ENDPOINT). According to endpoint documentation, the RC4 encryption is deprecated.

The fix was easy, on the 2012 server I changed the encryption to AES:

ALTER ENDPOINT [Mirroring]
    FOR DATA_MIRRORING ( ENCRYPTION  = REQUIRED ALGORITHM AES);
GO

Note that if I had changed the encryption on the 2016 instance to use RC4 encryption, the Availability Group would no longer work.

Once I made this change, mirroring was up and running. All my prep work paid off, as the upgrade last weekend took minutes once we confirmed all services were shut down and users were out of the system. We had minimal post-upgrade issues to work through, and my next step is to enable Query Store 🙂 Hooray for 2016!

4 thoughts on “Endpoints for Mirroring and AGs in SQL Server 2016

  1. Heh, yeah, we ran into this issue as well. It’s a pity that the AG setup, while verifying/creating the endpoints, doesn’t check the encryption algorithm.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

A Fond Farewell

If you haven’t guessed from the title, I’m writing this post because I am leaving SQLskills. This Friday, January 14th, is my last day, and

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.