SQL Server 2016 Basic Availability Group with non-default endpoint port

Antecedent

During one of my latest implementations I configured a multi-subnet SQL Basic Availability Group (BAG) configuration in Azure. I also wrote a blog about that what you can read HERE. After that configuration, I was thinking about how to deploy 2 BAGs in the same SQL Instance using separate ports. As we know there is a possibility to set the SQL instance to listen on more than one port by adding the list of ports separated by coma. After setting the SQL Instance I started to configure Windows Server Failover Cluster (WSFC) feature on the servers, deploy the test databases and configure the BAGs.

Symptom

I did not have any issues while deploying the first Availability Group. Then I started to configure the second one. When went over the configuration I knew I must modify the Endpoint port for the second Availability Group. The default is 5022, so I decided to go with 5023. Then I finished the configuration and started the deployment process, which suddenly failed on the “Creating Endpoint” step. Hmm… What happened? The question appeared in my mind and started to run over on the configuration again especially checking the configuration of the Endpoint. When everything looked OK, I started the deployment again. And it failed again. Hmm… again.

Investigation

OK then let’ start with the Event Log. In the Application log, I saw a Critical entry, which informed me that the endpoint cannot be created using port 5022, because it is already in use by another process. It made me raise my eyebrow. Why is this trying to deploy this availability group using port number 5022? I went over on the configuration details again and I confirmed that I set the port to 5023. I started the deployment, which obviously failed again. Next thing which came to my mind is to somehow see what the SQL is using when it does the configuration. On the last page of the wizard we can request the SQLCMD instead of running the deployment. And voilà… the script shows 5022 as port number everywhere.

Resolution

It looks it’s a bug in the UI of the wizard, so we need to avoid to use it until it is fixed. I looked for the port settings in the script file. As I remember there were 4 lines where the port was configured to use 5022. The only thing you need is to take that script and modify it to use 5023 and execute it. STOP! Not just yet. This script cannot be executed in a normal SQL query window. You need to use SQLCMD window to properly execute this script and finally configure the 2nd BAG which is now using port 5023 for endpoint.

Enterprise Mobility Workshops - 24th November 2015 - London | 9:00am – 3:00pm

Leave a Reply

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