In the previous part [Part 4] we have installed and configured AlwaysOn Availability Group. This is how our final network diagram looks:
As you can see, SQL server is listening not only on the default 1433 and 1434 ports, but also there is an established two way communication using port 5022 [If you recall from the preveous parts this is a port we have accepted by default for the Endpoint configuration – Availability Group internode communication]. And what is the most important, SQL server is listening on 172.31.65.151:1433, and that is our Listener.
I’ll be using my Domain Controller as a machine imitating a client. First, lets do a simple ping to a listener name:
Now lets use SSMS to connect to a server using a Listener name [port 1433 is used by default]:
Now lets create a “Universal Data Link” with “SQL Server Native Client 11.0”
When connecting to an availability group listener, SQL Server Native Client attempts to establish connections to all IP addresses in parallel and if a connection attempt succeeds, the driver will discard any pending connection attempts. Microsoft recommends to always specify MultiSubnetFailover=Yes [or MultiSubnetFailover=True, depends where you do it] option when connecting to a SQL Server 2012 availability group listener. MultiSubnetFailover enables faster failover for single and multi-subnet AlwaysOn topologies. During a multi-subnet failover, the client will attempt connections in parallel. During a subnet failover, SQL Server Native Client will aggressively retry the TCP connection.
Speaking about drivers. Microsoft gives this driver feature table:
OK, so far so good. Now we need to test what is going to happen to a connection when the Primary node fails. Before starting I’ve ammended my Availability Group failover parameters in the WFC console. The default timeouts are very long. Here is how parameters were set for the IP resources:
By the way, pinging a Listener is not a totally correct test, because your OS caches the DNS an therefore you will get no ping back because the node is down, but that is why we use MultiSubnetFailover=True option in the application, it will chek all available Listener IPs and find the active one.
In the next post I’ll make a simple application and do some more testing. See ya 🙂