Re: SQL Server Availability Groups

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.

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.

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.

> > Regards
> >
> >
> > Alistair Roberts
> >
> 
> Frediano
> _______________________________________________
> FreeTDS mailing list
> FreeTDS <at> lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 

Cheers,

Paul-Andre