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
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.
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
just to update you. if i connect to the listener on the named instance and include the port it connects successfully
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.
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.
This is also discussed by SQLCat team here:
https://blogs.msdn.microsoft.com/sqlcat/2014/02/03/alwayson-availability-groups-listener-named-instances-port-numbers-etc/
17 People are following this question.
