1

In my scenario, I have two servers in each of them two SQL Server instances:

  • Server 1: Instance 1 and Instance 2
  • Server 2: Instance 1 and Instance 2

I created availability groups between the instances like this:

  • Instance 1 from Server 1 with Instance 1 from Server 2: Availability Group 1

  • Instance 2 from Server 1 with Instance 2 from Server 2: Availability Group 2

And I have create a two listeners:

  • Listener 1, for Availability Group 1
  • Listener 2, for Availability Group 2

The problem is when I try to connect with the Listener 1, it connects me to the Availability Group 1, but when I try with Listener 2 it also connects me to Availability Group 1, not to Availability Group 2.

I don't understand what I'm doing wrong.

  • You need to ad some details about your network configuration. – user_0 May 6 at 10:10
0

This can happen due to the TCP ports configuration of your instances and listeners

If one of the instances of SQL Server is already listening on TCP port 1433 via the instance listener and there are no other services (including additional instances of SQL Server) on the computer listening on port 1433, this will not cause a port conflict with the availability group listener. This is because the availability group listener can share the same TCP port inside the same service process. However multiple instances of SQL Server (side-by-side) should not be configured to listen on the same port.

Source

In short, you can have both your listeners listening on port 1433, as long as you change the tcp ports for the instances to different ports like 1533, 1633.

You could also use different port numbers than 1433 but then you need to specify these in the connection string(s).

In the case of "one instance, one listener" you could be using the same port for both.

0

The most likely cause is your port configuration. At least one instance on each server will be running on a non-standard port (something other than 1433) but if both of your listeners were configured with the default configuration, they will be listening on port 1433. This causes a port conflict and whichever SQL instance started first and started listening on that port is the one that will respond to connections.

From your description, I would surmise that Instance 1 on both servers is listening on port 1433 and both listeners are as well. When you connect via either listener name, it connects on port 1433 which is Instance 1 in both cases, because you cannot share ports across instances of SQL Server.

To resolve this problem, first, you should configure your SQL Server instances to use non-default ports (link). Finally, you should reconfigure your Listeners to use different ports (link). Your listener ports can be the same as your instance ports, but they cannot conflict with other instances or listeners on the same server. So Listener 1 can use the same port as Instance 1 but must be different from Instance 2 and Listener 2.

Note that when you use non-default ports, you need to specify the port number in your connection string - Server=tcp:Listener1,1456;Database=MyDB;IntegratedSecurity=SSPI

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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