AlwaysOn failover

Question

SQL server standard 2017
AlwaysOn Asyn

Primary SQL 2017
Secondary SQL 2017

Primary and Secondary are 2017, in the secondary server there is another SQL 2012 instance (default) which is independent to SQL 17 installation and not part of AG.

I did a forced failover to secondary, then tried to connect new Primary(where we have two instance), the connection request goes to another instance (2012) on the same machine that is not part of AG? What could be the reason? Used the listener ip for the connection.

Thanks,
Ashru

Answer 1

After failover the connection request goes to 2012, when we expect it to SQL 2017 secondary (new Primary).

This seems highly unlikely I am sure you are using incorrect connection string. If you are using listener then connect using that. I hope listener name is unique. What is instance name of SQL Server 2012, SQL Server in AG and that of Listener. Hope they are different ?

EDIT: Answer after follow up question

OK, now i understand your scenario. You have SQL Server 2012 running as default instance and listening on 1433 and Listener using some different port. In this scenario and also as documented in BOL document you would have to use port no with listener name. Something like ListenerName,port_no. In your case port no is 1444 so in SSMS while connecting with listener the name should be listener_name,1444. This should connect you to correct instance

If the port is the default port of 1433, then you do not have to specify a port number when connecting to your listener. However, if the port is not 1433, then the port must be specified in the connection string

Now in your case since you are not specifying the port no while connecting using listener name and you have a default instance listening on default port 1433, SQL Server is getting confused and directing it to default instance. Ideally it should flash error but it is not. This is something which is strange. This happens in scenario where you have multiple instance of SQL Server running on same machine and using default port 1433.

Moral: Always use port no with listener name if listener is using non default port (default port is 1433) and the you would connect to correct instance.

Answer 2

I use the Listener IP in connection string. Even when we connect to SQL directly using Listener IP/Name the connection goes to 2012.

SQL 2012 instance and AG primary and Secondary are different , Listener name also different.

I was wondering if we have to make any specific change in Endpoint, or listener subnet etc when there are multiple instances in AG nodes?

Thanks,
Ashru

Answer 3

See the screen shot, you can see that when I connected to listener, version shows 11.xx. The SQL 2012 is the default instance on the machine.