Create multiple AOAG and listeners on 2 node cluster

HI There,

I have a 2 node cluster with 2 SQL Server instances installed (1 default, 1 named). with that i have 2 AlwaysON availability groups and 2 listeners with 2 separate IP's.

my issue is if i connect to the listener configured for the second named instance it connects to me to the default instance.

my setup is 2 node cluster. 2 installs of SQL SErver 2012 on each node. 2 seperate AG's created. 2 seperate listeners within the failover cluster administrator with dependencies added for each on the AG's.

has anyone encountered this issue before? any solutions?


more ▼

asked Dec 05, 2017 at 06:41 PM in Default

avatar image

21 1 2 5

First thoughts, is did you create both listeners to point to port 1433, my guess is so and as such both listeners will send you to the default instance. Try changing the named instances listener to the same port used by your named instance, and use a static port not dynamic.

Dec 06, 2017 at 09:23 AM anthony.green

thanks for the reply but yes the listener on the named instance is running on a separate port to the default instance. I've attached a doc of the setup[link text][1]

again, my issue is both listeners connect me to the default instance [1]: /storage/temp/4411-mssql-aoag-config.docx

Dec 06, 2017 at 08:03 PM niall5098

just to update you. if i connect to the listener on the named instance and include the port it connects successfully

Dec 06, 2017 at 08:22 PM niall5098

The doc doesn't show the port only the instances and the IP's.

In SSMS right click the listener and check the ports, are both listeners on port 1433? If so change the listener on the named instance to be the right port.

Dec 07, 2017 at 09:29 AM anthony.green
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I've seen this before.

In my case I had 2 instances on a server, say SERVER1\MSSQLSERVER (default instance) and SERVER1\NAMED.

Both had separate listeners, say AGLIST1 for the default instance and AGLIST2 for the named instance. I found that even if I connected to listener AGLIST2, I was connected to the primary replica for the AG that was under AGLIST1.

If I connected to AGLIST2\NAMED, then everything was fine. This suggested that the resolution of AGLIST2 was somehow going to the base server rather than the instance - and in essence that is what is happening. So this could be a workaround for you.

The other, and the root of the 'problem', is that the SQL server was configured to listen on all IP addresses (which is does by default) - see the answer by davidbaxterbrown here https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fc55c447-a7a2-4b84-a9c0-352f84511e9d/always-on-listener-on-named-instance-do-i-have-to-specify-the-port-while-connecting-a-listener?forum=sqldisasterrecovery

So you can either change this setting so that each instance only listens on the IP addresses associated, or use the named instance with the listener name.

more ▼

answered Dec 07, 2017 at 02:35 PM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 05, 2017 at 06:41 PM

Seen: 61 times

Last Updated: Dec 11, 2017 at 05:49 AM

Copyright 2018 Redgate Software. Privacy Policy