Re: SQL Server Availability Groups

2016-05-12 23:27 GMT+01:00 Paul-Andre Panon <[email protected]>:
> Alistair Roberts <alistair.roberts <at> ladbrokes.com.au> writes:
>
>>
>>
>> -----Original Message-----
>> From: FreeTDS [mailto:freetds-bounces <at> lists.ibiblio.org] On Behalf Of
>> Frediano Ziglio
>> Sent: Friday, 20 March 2015 8:25 PM
>> To: FreeTDS Development Group
>> Subject: Re: [freetds] SQL Server Availability Groups
>>
>> 2015-03-17 1:51 GMT+00:00 Alistair Roberts
>> <alistair.roberts <at> ladbrokes.com.au>:
>> > Hi,
>> >
>> >
>> >
>> > Does FreeTDS support SQL 2014 Availability Groups?
>> >
>> >
>>
>> No, but it's open source so adding it would be good.
>>
>> >
>> > We are looking at migrating from SQL 2008 to SQL 2014 and then use
>> > Availability Groups for the databases.
>> >
>> >
>> >
>> > Ideally we are looking at using a sole IP but we may need to go with
>> > the multi-subnet approach for the AG listener.
>> >
>> >
>>
>> Do you have any details of the protocol and how does it work?
>>
>> The availability group has a listener IP and port (often 1433) that SQL
>> connections are directed to. It is possible to have read only replicas of
>> the same database. So if a connection has ReadIntent in the connection
>> string it will go to a read only version.
>
> 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.

> 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.

> 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?
Do you have a similar environment setup?
Could you do some tests?
Could I have some network capture?

Frediano