Sql-server – SQL Server 2014 Always on listener on named instance for default port

availability-groupssql serversql server 2014

This is my issue.
I've set up a test environment with SQL Server 2014, 2 named instances, WSFC and AG. They're set up like this.

 sqllab04\inst01 - sqllab05\inst01 - Listener VT-DB-LAB02 (port 1433)
 sqllab04\inst02 - sqllab05\inst02 - Listener VT-DB-LAB03 (port 1433)

Both listeners have it's own IP with DNS set up (dns name above).

INST01 on both 05 & 06 are set with port 1433 as per default in config manager. 
INST02 on both 05 & 06 are set with port 1432 as per default in config manager.

enter image description here

Listen All is active on both and on INST01 it's set TCP Port 1433 on all and INST02 is 1432 on all.

The problem is following:
When we connect using ssms to VT-DB-LAB02 we can connect without any issues.
When we then connect to VT-DB-LAB03 we can connect but the connection goes to VT-DB-LAB02

enter image description here

From what I understand the issue is that inst01 is using port 1433 as default instance and is listening on ALL 1433 but what I don't know is how to get around that.

We still want to use port 1433 for inst01 and not change it to another port which I believe would solve it. I know that it's not the most secure way to set it up but I'm just testing right now.

Any suggestions? Let me know if anything is unclear or if I've missed some information.

Best Answer

This is happening because you have configured LAB03 listener to listen on port 1433 which is default port of default SQL Server instance, that is why when you connect using the listener it is connecting to SQL Server which is listening on port 1433. While Microsoft allows you to use default port 1433 for listener but this is not a correct thing to do specially when you have multiple instances and listeners. To get around this problem you have to change the port number of listener VT-DB-LAB03

sqllab04\inst02 - sqllab05\inst02 - Listener VT-DB-LAB03 (port 1433)

You can choose free port and configure listener to listen on this port after you do so the issue will resolve.

If you are choosing a non default port for listener you would have to use port number in connection string

Quoting from Docs.Microsoft

Also, since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.

You can also designate a non-standard listener port; however this means that you will also need to explicitly specify a target port in your connection string whenever connecting to the availability group listener. You will also need to open permission on the firewall for the non-standard port.

If you use the default port of 1433 for availability group listener VNNs, you will still need to ensure that no other services on the cluster node are using this port; otherwise this would cause a port conflict.

I suggest you also read AlwaysOn Availability Groups, Listener, Named Instances, Port Numbers, etc.

Related Question