4

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.

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
6
  • I have thought about this aswell but isn't there some way that I can sort the port 1433 out on inst01 instead? So that when we connect to inst01 we have to specify the port, for example sqllab04\inst01,1433 just like we have to do if we connect to sqllab04\inst02,1432? I've read some post about this that u had to tell the default instance to change in tcp/ip protocol so it doesn't "Listen All" and some other changes but the instruction there wasn't really clear.
    – Jonathan
    Jun 13, 2017 at 10:05
  • So as you said inst01 is not s problem the problem comes when you connect via listener to inst02 so can you no change port number of listener LAB03 you already have inst02 listening on 1432 ?
    – Shanky
    Jun 13, 2017 at 10:09
  • Well the problem is still that if we change listener on inst02 to 1432 we have to specify the port when we connect. Just tried it and we had to enter vt-db-lab03,1432 in order to connect correctly. We want to avoid having to specify the port when connecting via listener.
    – Jonathan
    Jun 13, 2017 at 10:36
  • To avoid port number while connecting via listener you have to use default port 1433 for AG, SQL Server and listener. This you have already done for one of the instance inst01 so you cannot do this for inst02. You have to specify port number otherwise how is listener going to differentiate. If you are using non default port you have to mention the port while connecting via connection string
    – Shanky
    Jun 13, 2017 at 11:00
  • 2
    Well I did try one thing. I set TCP/IP for inst01 to port 1431 and for inst02 to port 1432. I set port 1433 for both vt-db-lab02 and 03. Now that I connect to either of the two AG listeners I don't have to specify a port since the default instance doesn't intercept the connection.
    – Jonathan
    Jun 13, 2017 at 11:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.