2

All, Thanks in advance I have two instances of SQL Server 2012 SP1 on a three node WSFC with a HAdr group on each instance with a corresponding listener. Instance 1's listener points to Instance 1, no issue. Instance 2 listener also points to instance 1. I'm at a loss why listener #2 is showing databases for Instance 1. Ive recreated instance 2's HAdr groups from scratch but still no change. Here is some additional information -

  1. Both instances Database mirroring endpoints are configured on different ports.
  2. The SQL Server instances are named instances also using different ports.
  3. Listeners have different names and different IP addresses and ports.
  4. Opening up each SQL instance on each of the three nodes show the correct HADR information and databases.

The Listener seems to be getting confused and is looking at the wrong instance.

Any help would be greatly appreciated thanks James

  • Are Instance1 and Instance2 both replicas for both availability groups? – Thomas Stringer Jun 27 '14 at 18:36
  • 1
    Definitely need much more information than given. Could you please run the following query and post back the results? select ag.name as AG_Name, agl.dns_name AS Listener_Name , ar.replica_server_name FROM sys.availability_groups ag inner join sys.availability_group_listeners agl on ag.group_id = agl.group_id inner join sys.availability_replicas ar on ag.group_id = ar.group_id – Sean Gallardy Jun 28 '14 at 22:17
  • Your listener looks to be doing exactly what it is supposed to do - point to the Primary replica. The only way to make it point to the secondary is to A.) configure a readable secondary B.)connect with applicationIntent=readOnly – Steve Mangiameli Oct 8 '15 at 21:03
3

An Availability Group Listener simply connects the user to a database instance. Once they are connected to the instance they can access all the databases on the instance. It doesn't matter of the databases are part of the availability group or not. This is by design.

In other words everything is working exactly as it should be.

1

Hi I got solution to this problem. What I did is go to SQL Server configuration manager and go to Instance1 of DR site and remove port 1433 from IPAll. Restart both the instance at DR and now it will point correctly.

Please try and let me know. Khaled Bakhtiar.

0

connect your Named SQL Instance 2 listener like below

"listener name\static port" or "listener name\Namedinstance"

then Instance 2 will point your named instance database.

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.