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.
It can connect from SQL 1 server, which is the primary for AG.
By "connect", do you mean it can ping AG-LISTENER
from SQL1
?
It sounds like what your problem might be is with the port number you chose for your listener. By choosing 5525, you are selecting a non-default port (1433 would be the default).
So when you try to connect to the listener, what does your connection string look like? I'm guessing it looks something like this:
data source = ag-listener; initial catalog = ...
You have two options here. You can either be explicit with your listener's port number:
data source = ag-listener,5525; initial catalog = ...
Likewise, if you're testing this out with SQL Server Management Studio (SSMS), then for the Connect to Server dialog box, instead of putting in ag-listener
for the Server name text box, put in ag-listener,5525
.
Or you can change the port that your listener is listening on to 1433 (read the below BOL reference before considering this change):
alter availability group YourAvailabilityGroupName
modify listener 'AG-LISTENER'
(
port = 1433
);
It is worth noting when you can use the default port (1433). Take a look at this reference on BOL explaining when you can and can't use 1433 for the listener (excerpt copy/pasted below for reference):
You can configure the default port to 1433 in order to allow for simplicity of the client connection strings. If using 1433, you do not need to designate a port number in a connection string. Also, since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.
(portions omitted for brevity)
If you use the default port of 1433 for availability group listener VNNs, you will still need to ensure that no other services on the cluster node are using this port; otherwise this would cause a port conflict.
EDIT: If the above isn't your problem (as seen from your comment below) then, after you look through the logs, I'd say the next best course of action is to start looking at the network traffic to see what is (and isn't) happening. You can use a network monitoring tool like netmon to accomplish this.
Another thing I'd do, and I realize you said the firewall isn't a problem, but I'd see if the port is actually listening (my favorite tool for this is portqry).
Best Answer
I don't believe this configuration setting is sent to the server. It controls the client's interpretation of a DNS lookup returning multiple IP addresses, and there is no functional need for the server to be informed about it. And the client doesn't send arbitrary name/value pairs to the server. The fields that a client sends to the server are dictated by the TDS protocol, and it would require a protocol revision to add new ones.
Also while properly setting MultSubnetFailover at the client continues to be a best-practice, newer SQL Server client libraries have changed their behavior to mitigate the issues arising from not setting MultiSubnetFailover. See Using Transparent Network IP Resolution. Newer clients will have only a brief delay in connecting when the first IP returned from DNS is not the active AG Listener, instead of the old behavior of waiting 20-30 seconds before attempting to connect to the second IP.