Skip to Content.
Sympa Menu

freetds - Re: [freetds] SQL Server Availability Groups

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Paul-Andre Panon <paul-andre.panon AT avigilon.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] SQL Server Availability Groups
  • Date: Wed, 18 May 2016 22:56:19 -0000

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




Archive powered by MHonArc 2.6.24.

Top of Page