FreeTDS Development Group

[Paul-Andre Panon <paul-andre.panon AT avigilon.com>]

Sorry about the long delay in my reply but I got busy on some other stuff.  
Congratulations on 1.0. I'm impressed you managed to include some support for 
this at the last minute.

2015-05-13 12:08 GMT-07:00 Frediano Ziglio wrote:
>
>2016-05-12 23:27 GMT+01:00 Paul-Andre Panon <Paul-Andre.Panon AT avigilon.com>:

>> In a multi-subnet cluster configuration, a separate IP address is 
>> assigned to the AG listener for each subnet containing a cluster node, 
>> where the listener IP address is consistent with the subnet of the the 
>> cluster nodes.
>> So, for CIDR/24 subnets, if you have
>>
>> Cluster Node A : IP: aaa.aaa.aaa.xxx
>> Cluster Node B : IP: bbb.bbb.bbb.yyy
>>
>> You will have two entries in DNS as the A record for the AG Listener:
>> aaa.aaa.aaa.mmm and bbb.bbb.bbb.nnn
>>
>> You can have more than one secondary AG replica as well, resulting in 
>> more than 2 entries in the A record.
>>
>> The primary replica for the AG will respond to its listener IP address 
>> on the designated SQL Server port but the secondary replica node(s) 
>> will not respond. (You can configure a different port from 1433 but 
>> you want to be consistent across all the cluster nodes since you can 
>> only specify one port in the db connection string). The client driver 
>> needs to asynchronously attempt to open a TCP connection to all 
>> addresses in the DNS A record, then continue with the address which 
>> responds and discard the other connection attempt. The Microsoft 
>> driver implementations also start additional new TCP connection attempts 
>> to all nodes every second.
>>
>
>It make sense. Actually addresses are tried in sequence, not all together.
>It's not clear the 1-second attempts. I suppose they happens if all first 
>ones failed.
Well that's not what the technet article says. Look at the first two 
paragraphs in the section Connecting with MultiSubnetFailover on 
https://msdn.microsoft.com/en-us/library/hh205662
 "During a multi-subnet failover, the client will attempt connections in 
parallel."

Also, I interpreted "During a subnet failover, will aggressively retry the 
TCP connection." to mean that it opens multiple connections to each IP 
address. You can't just close the old one after 1 second because it might 
actually take more than one second to connect. The reason is that during a 
failover from one node to another (even with a controlled manual failover) 
the transition is not immediate and takes a few seconds. So rather than have 
the client be dependent on the exponentially increasing delay between packets 
of a standard TCP connect, it somehow makes more  SYN packets be sent out 
with a 1s spacing. I'm assuming it does this with additional connections but 
maybe there's another way to do it. I haven't looked at what the MS JDBC 
driver code does, only at packet captures. I only ever saw it happen once in 
a packet capture, so it might have just been due to a SYN packet dropped by 
the network. 

>
>> If the ReadIntent option is set in the connection string then, once 
>> the connection to the primary (RW) replica is established, there is a 
>> new TDS exchange that you can use to find out the address of the read only 
>> replica.
>> Once you have that information, the driver can close the connection to 
>> the primary replica and re-open a new TCP connection to the read-only 
>> replica.
>>
>
>It's not clear to me how the driver detect if a server is primary or not and 
>how it gets details on the replica, I miss some bits in the protocol but I 
>don't thing are hard to find.

Only the cluster node hosting the primary replica answers TCP connection 
requests. So out of N IP addresses in the A record, only the primary replies.
This is why you need to try them in parallel, because otherwise you need to 
wait for the connection timeout on each address, and timing out on 2 or 3 
secondary addresses in sequence can take you past the login timeout before 
you get to the primary's IP address.

You need to set up read-only routing to indicate what secondaries should 
handle read-only requests. I didn't set it up because we didn't see a need 
for it. I do remember seeing a link that talked about how it worked with TDS, 
but I can't find it right now. Here's a related MSDN article on the 
server-side configuration.
https://msdn.microsoft.com/en-us/library/hh710054
You can see with the read-only route URL syntax for each replica, that it 
indicates a URL directly to the cluster node designated as a secondary 
replica (so it's a different computer name, and potentially port, as the 
listener).

ALTER AVAILABILITY GROUP [<your availability group>]
MODIFY REPLICA ON N'<your availability group replica>' WITH 
(SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'tcp://<host>:<port>'))

Then the read_only_routing_list indicates multiple replicas, each with a URL 
to the hosting cluster node. The question is: what query does the stock MS 
client driver use to get the routing URL list so it can re-open a new 
connection to an appropriate cluster node? You could probably get that 
through a packet capture, but I didn't need or research that. I didn't find 
it in a web search.

>> SQL Server 2016 Standard Edition apparently uses a limited version of 
>> this protocol to completely replace the (no longer supported) SQL 
>> Server 2008 database mirroring functionality.
>>
>>
>> While ReadIntent support would be nice, much more important is 
>> supporting the multi-subnet connection model. A standard driver will 
>> only try the first entry in the DNS A record - if it is not the 
>> primary replica, no answer will be received and the connection attempt 
>> will fail. Since DNS uses round-robin ordering for multiple address 
>> records, there's at best a 50% chance that the connection attempt will 
>> fail.
>>
>
>Actually ReadIntent is supported, at least you can pass to the server.
>Also current master will attempt (after a failure) to connect to second host.
>So you'll get a connection to the first available.
>
>What happens if the first that reply is a replica and you don't have the 
>ReadIntent set?
You will never get an answer from the listener IP address of a secondary 
replica. You can see the same thing with ICMP echos actually (as long as you 
don't block them with the Windows firewall). Ping the two+ IP addresses of an 
AG Listener and only the address associated with the primary replica will 
reply.

>Do you have a similar environment setup?
Yes. I was trying to do something similar for the jTDS driver. I ran some 
captures of some testing with the MS JDBC driver to observe its network 
behavior. My jTDS seemed to work if I used a Java NIO non-blocking socket for 
the connection and then switched them to blocking sockets after the 
connection was established, however that had some overhead. To avoid that 
overhead, I then tried to spin up a thread for each connection with standard 
blocking socket connects, but my thread synchronization was causing 
problems/interactions with the multiple threads of the calling application. 

>Could you do some tests?
You probably can soon as well. SQL Server 2016 is to be released on June 1, 
so you could either use the developer or standard editions to set up a test 
cluster. The developer edition is supposed to be free and has full 
enterprise-equivalent functionality. If you have a VM environment, you should 
be able to set up a small 2 node cluster for testing.

>Could I have some network capture?
I do still have one Wireshark traffic capture from my JDBC tests, but it's 
not the one that shows the second retried SYN after 1sec. It also is a 
straight connect to the primary, not a read-intent connection to a secondary. 
Let me know where you want it. I don't have anything from any MS drivers for 
other frameworks like Native/OLE, or ODBC.

>Frediano