There's two parts to this question:
First, can you use the Failover Partner connection string tip with AlwaysOn Availability Groups? No. AlwaysOn AG's "Listener" technology is the replacement. Have your connection strings point to the listener name and they'll always get the primary replica. (For the next part of this answer, I'm assuming you're using the listener name - if you're not, start, heh.)
Second, why do some queries fail to connect to the listener? This has to do with the number of DNS entries for the listener. All possible subnets for the listener will be in DNS at all times. If you've got a listener in 192.168.1.X and another in 192.168.100.x, both listeners will always be in DNS. By default, your clients will try connecting to each of the DNS entries serially, and not always in numeric order. If you've got a 30-second connection timeout, it's possible that your app will only try one of the IPs and then fail before it has the time to try the second one.
If you want to try connecting to all possible IPs simultaneously, check out the MultiSubnetFailover = True options for the SQL Server client as described here: http://msdn.microsoft.com/en-us/library/hh205662.aspx
Otherwise, you'll need to increase your connection timeout to account for the multiple IPs.
Update Feb 27: the question added, "I was therefore trying to come up with a way to avoid manual intervention for these (replicated) DBs should a failure of the replica occur and the other databases which are part of an AG failover."
Ooo, unfortunately, no, if the databases aren't part of the Availability Group, you're going to be doing manual work in order to fail over. One popular option is to use a DNS record, and just repoint the DNS record at whatever server is currently hosting the primary copy of the databases.
To start with there is nothing called as Always On or Always ON availability groups it is simply called as Availability groups.
- Always On Clustering and Always On Availability Groups are 2 separate concepts. Clustering is a HA solution and AG is a DR solution. Is the Always On Clustering same as Windows server clustering?
Yes you are correct. Always on clustering means you have created availability groups on database(s) which is part of failover cluster instance (FCI) and availability groups simply means two standalone nodes which are part of WSFC or not are configured to form AG, there is no FCI here.
- To create an Always On cluster - we have to launch the installer and choose the "New SQL server failover cluster installation", then on every new node we need to launch the installer and choose "Add Node"
That is basically creating an SQL Server failover cluster instance. Yes after you have created an FCI you can go ahead and add the databases residing on FCI to an AG. This is what you are calling as Always On cluster and this is what official MS doc calls it.
For point 3 please refer to above two quotes. Again note the difference between AG and AG residing on FCI.
- Assuming we have a fresh SQL instance (with no HA/DR) and are planning to configure only AG, then we first need to ensure that the "Failover clustering" windows feature is enabled on each participating node.
If you are planning to configure ONLY AG you do not need failover cluster instance. AG can be configured with 2 or more nodes which are part of WSFC or not (starting from SQL Server 2017 we have clusterless AG). Remember an FCI will always require some kind of shared storage, OTOH AG does not requires any kind of shared storage.
- In above scenario, "Failover clustering" windows feature is enabled so is clustering a pre-requisite for AG? Is the concept of clustering in Point 2 and Point 4 the same.
WSFC is prerequisite for AG but starting from SQL Server 2017 you can have AG without WSFC but that is not a real AG. WSFC is prerequisite for FCI though.
EDIT:
If I wish to configure both HA/DR using Always On (AG) and Failover Clustering, then is best practice to follow Point 2 and then point 4 or the opposite way? Also, should we use both the virtual cluster name and Listener or either one if sufficient?
You need to first configure WSFC then install FCI like you mentioned in point 2 and point 4 then create database and then configure availability groups. Virtual cluster name is for FCI while Listener is for AG. You cannot configure cluster without virtual cluster name you have to have one, but you can have AG without listener. The only work of listener is to direct connections to primary replica which can be on any of the node.
What is difference between "SQL server failover cluster installation" and windows clustering?
Windows server failover cluster(WSFC) is windows server feature on which whole "clustering" technology is based while FCI is at SQL Server level which leverages WSFC for failovers to provide AH to SQL Server instance.
Best Answer
This is a difficult scenario to truly handle. I would probably start with running a job on your secondary replica that checks the state of all the databases, and should it become primary for one of them then performs the ALTER AVAILABILITY GROUP [AG] FAILOVER; command for all of the others.
The biggest challenge with this is also running jobs on the primary, and taking care to not accidentally try to fail one of those databases back. You could manage this by checking the AlwaysOn extended event session and looking for failovers there, and do not do work if the database was failed away (as the most recent captured event), this way you don't end up bouncing the databases around all over the place.
You can use sys.fn_hadr_is_primary_replica to check and see if any particular database is the primary on your system.