0

Here is my case. I have Server1 and Server2, which were installed 4 SQL instances as follows:

Server1

  • Server1\SQL1
  • Server1\SQL2

Server2

  • Server2\SQL1
  • Server2\SQL2

The Server1\SQL1 and Server2\SQL1 are replicas in SQLAG1 which is listening on the listener xxx.xxx.xxx.54

The Server1\SQL2 and Server2\SQL2 are replicas in SQLAG2 which is listening on the listener xxx.xxx.xxx.55.

But when I use SSMS to connect to xxx.xxx.xxx.54 or xxx.xxx.xxx.55 It always connects to the Server1\SQL1 which is the primary replica of SQLAG1.

Any thoughts? Do I need to install more AGs on the same servers? How can I separate them by listeners?

  • And what's the IP network configuration for the instances in SQL Server Configuration Manager? In particular what IPs and Ports are the instances listening on? – David Browne - Microsoft Aug 20 at 22:52
1

It sounds like you have set the listeners to use the same port, probably the default 1433.

When you create an AG listener you specify a port for it to listen on. If you have multiple listeners on multiple AGs, they will each need to listen on a different port otherwise you will only be able to connect to the listener that started first and gained control of the port.

You should also ensure configure your SQL Server instances to listen on a specific port, as named instances will use a dynamic port by default. Specifying the port helps you document and control your environment better.

Info on configuring SQL Server to use a specific port

For your listener, run the below query against the primary of each AG to get the current port configuration:

SELECT ag.name AS [Availability Group], agl.dns_name AS [Listener DNS Name], 
agl.port AS [Port]
FROM sys.availability_group_listeners agl 
INNER JOIN sys.availability_groups ag ON agl.group_id = ag.group_id 
ORDER BY ag.name, agl.dns_name 

And use this code to update your listeners to use a specific port

USE [master] 
GO 
ALTER AVAILABILITY GROUP [** AG name here **] 
MODIFY LISTENER N'** Listener name here **' (PORT=1433); -- or change to whatever port you are wanting to change to. 
GO 

Info on changing listener ports here

0

I think, you need verify your AG configuration again and update your question. It cannot be possible when you connect xxx.xxx.xxx.55 it goes to SERVER1\SQL1 unless the ip xx.55 is the IP address of (SERVER1) in which case you would not able to create listener as the same IP already assigned.

As i understand:
AG1 (SERVER1\SQL1, SERVER2\SQL1) - Listener IP: 54
AG2 (SERVER2\SQL2, SERVER1\SQL2) - Listener IP: 55

Since it's impossible for AG2 to have SERVER1\SQL1 as from 1 Node we can NOT have 2 Instances as Replicas in same Availability Group

Consider port configuration properly:

  1. SQL Ports - Where user connections directly made to replica
  2. End-point ports - Where database mirroring takes place between replicas
  3. Listener ports - VNN for group of Replicas

Make sure all these ports would not conflict with each other on every server.

Recommended Example:
SQL Ports: 1533, 1534
Endpoint ports: 5022, 5023
Listener ports: 1433, 1434, 1435 etc..

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.