Q: When using SQL Server AlwaysOn across multiple sites, how do the clients communicate to the current active copy of the database?
A: When using SQL Server AlwaysOn Availability Group availability group listener, which performs asynchronous or synchronous replication of data between SQL Server hosts, it's common for the active copy of the database to move between servers.
Clients using the database need to be able to connect to whatever copy is active, and this is achieved through the SQL listener, which is the IP address actually used by the clients. The SQL listener then facilitates the communication to the active database.
This is more complicated if the AlwaysOn availability group spans multiple sites, as it's likely that different IP subnets are used in each site. This means the SQL listener cluster resource has multiple IP addresses assigned, one for each subnet.
But this does not mean DNS updates are required for SQL clients to use a changed IP address for the SQL listener if the listener moves between sites in the event of a failure.
Instead a change is made to the SQL listener resource to make all its IP addresses registered in DNS (i.e., its IP address in site A and in site B):
cluster /cluster:<ClusterName> res <SQL Listener name> /priv RegisterAllProvidersIP=1
or
Get-ClusterResource <SQL Listener name> | Set-ClusterParameter RegisterAllProvidersIP 0
When the DNS name of the SQL listener is requested, all the possible IP addresses are returned. The SQL client then uses the MultiSubnetFailover parameter (as documented at this Microsoft TechNet article) which causes the client to attempt to connect to all returned IP addresses in parallel and uses whichever one responds first (which normally means only one responds).
At a high level, the process works by registered all possible IP addresses for the listener to DNS, and then the SQL clients attempt to connect to them all during any connection or reconnection event. This gives the fastest reconnect in the event of any kind SQL listener failover.
