question

niall5098 avatar image
niall5098 asked

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? Thanks
sql serversql server 2012failoveralwayson
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
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
0 Likes 0 ·
just to update you. if i connect to the listener on the named instance and include the port it connects successfully
0 Likes 0 ·
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.
0 Likes 0 ·

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.