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?
asked Dec 05, 2017 at 06:41 PM in Default
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.
answered Dec 07, 2017 at 02:35 PM
Kev Riley ♦♦