SQL – Creating a SQL Server AlwaysOn on Amazon EC2, Part 5

In the previous part [Part 4] we have installed and configured AlwaysOn Availability Group. This is how our final network diagram looks:

AG15This is how Availability Group looks in the SSMS console:

AG16Now it is time to see AlwaysOn in action 🙂 Lets look at the network activities on the WIN2012R2-1 as this node is holding my Primary Replica at the moment:

AG17As 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, 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:

AG18 DNS was resolved to the WIN2012R2-1 node Listeners IP.

Now lets use SSMS to connect to a server using a Listener name [port 1433 is used by default]:

AG26AG20By the way, in case you have configured a different port number for a Listener, say 5033, then your connection string should specify a port number implicitly, like in this screenshot:

AG19So SSMS was able to connect and just to double check lets look at the TCPView on WIN2012R2-1 again:

AG21There is an established connection.

Now lets create a “Universal Data Link” with “SQL Server Native Client 11.0”

AG24Also successful.

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.

AG27You also might need to increase the value of loginTimeout to accommodate for failover time and reduce application connection retry attempts.

Speaking about drivers. Microsoft gives this driver feature table:

AG25Here, only OLEDB driver is problematic.

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:

AG33Now, I’m going to connect with SSMS [with MultiSubnetFailover=True option] to a Listener name and as you can see from the IP address this is node WIN2012R2-1, which is Primary at the moment:

AG28To imitate a failure I’m going to disable a network card that holds on WIN2012R2-1:

AG30As soon as the interface is down I try to do something in SSMS or even just Refresh and you will see that the Primary Replica has been moved in the backgroud to the other node – WIN2012R2-2:

AG32Pretty cool ha 🙂

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 🙂

Leave a Comment here

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s