SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Stairway to AlwaysOn Level 8: Segregate Mirror Traffic in AlwaysOn

By Perry Whittle,

The Series

This article is part of the Stairway Series: Stairway to AlwaysOn

AlwaysOn is a complex set of technologies that is often mis-understood. In this Stairway you will learn about the AlwaysOn technologies, how they fit into the High Availability stack, and how to make good use of them.

Welcome to Level 8 of this stairway, in this level we'll be look at segregating the Mirror traffic to a dedicated network, this is something you may consider implementing if you expect your AlwaysOn Availability group to have a high volume of transactional traffic.

In a busy AlwaysOn configuration, your Public\Client network will be busy enough handling client connections, but you may have a number of redundant network adapters. In this case you may want to use a dedicated network for your mirror traffic. A dedicated network provides security for the mirror traffic as well as providing the necessary bandwidth (well, at least it should anyway). In fact, this last point is the most likely stumbling block, "setting up a dedicated network that does not provide the bandwidth required", whether it be because the network was not load tested to ascertain the required throughput or inferior hardware was employed.

Before providing a dedicated mirror network, you'll want to undertake a requirements analysis and sufficient testing to ensure the solution is capable of handling anything you're likely to throw at it, especially if the WSFC\AO Group configuration spans multiple sites. To recap from previous levels of the stairway, let's look at our typical WSFC with an AlwaysOn group configuration. We have 5 nodes in the cluster shown below:

Each node has a default instance of SQL server and participates as a replica in the AlwaysOn group. For this level, each node has 3 network cards, 1 for each of the following network segments

Network IP Range
Public\Client 192.168.0.xxx
Private 10.10.10.xxx
Mirror 172.168.10.xxx

As my AlwaysOn group is expected to be very busy, I would like to segregate my mirror traffic down a particular network segment. The first step in segregating this network involves the Mirroring Endpoint creation.

You’ll remember from earlier in the Stairway, an instance may have only 1 Database Mirroring endpoint. Where multiple instances are installed on a server, the endpoint should have a unique IP address and TCP port. If all SQL Server instances on a server listen on the same IP address, a unique TCP port should be used. To segregate traffic, the Endpoints on each replica must be directed to use a specific IP address, which in turn would be bound to an appropriate NIC on the server. Before going any further, let's check the default that's used when setting up our Mirror endpoints. A default endpoint would be created using the following T-SQL:

CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL )
FOR DATABASE_MIRRORING (
    AUTHENTICATION = WINDOWS NEGOTIATE,
    ENCRYPTION = REQUIRED ALGORITHM RC4,
    ROLE=ALL);

Note: The default is to use all IPs available.

In order to segregate our mirror traffic we need to explicitly declare the endpoint IP address that we wish to use. The create statements here for my test system would be as follows. To use a specific IP address, use this code:

CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = (172.168.10.121) )
FOR DATABASE_MIRRORING (
    AUTHENTICATION = WINDOWS NEGOTIATE,
    ENCRYPTION = REQUIRED ALGORITHM RC4,
    ROLE=ALL);

To use a specific DNS A (host) record, as I've done here, use this code:

CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL )
FOR DATABASE_MIRRORING (
    AUTHENTICATION = WINDOWS NEGOTIATE,
    ENCRYPTION = REQUIRED ALGORITHM RC4,
    ROLE=ALL);

Create the endpoints on each replica and ensure they're started. When using the Availability Group deployment wizard, you will be required to provide details of any replicas you wish to join to the group. Once the group has been created, it's time to review our deployed configuration. Below are the replica details I have used.

Looking at the Endpoint URLs we can see slightly different names for the FQDNs. They are as follows:

  • StokeCSCLNode1M.StokeCS.co.uk
  • StokeCSCLNode2M.StokeCS.co.uk
  • StokeCSCLNode3M.StokeCS.co.uk
  • StokeCSCLNode4M.StokeCS.co.uk
  • StokeCSCLNode5M.StokeCS.co.uk

Each server has an IP address bound to the Mirror NIC, this IP is in the range 172.168.10.xxx. On the DNS server, an entry for each server's Mirror IP has a unique DNS entry created. To see an example,

If we ping StokeCSCLNode1.StokeCS.co.uk we get a reply from 192.168.0.121

If we ping StokeCSCLNode1M.StokeCS.co.uk we get a reply from 172.168.10.121

If we ping StokeCSCLNode2.StokeCS.co.uk we get a reply from 192.168.0.122

If we ping StokeCSCLNode2M.StokeCS.co.uk we get a reply from 172.168.10.122

and so on.

Moving on from the configuration, let's look at network statistics for our network connections. We want to see the mirror network actively sending bytes of data on the Primary. Testing a large table creation produces the following traffic statistics across the networks on the Primary.

Notice that the traffic on the Mirror NIC is extremely active and steadily increasing.

The remaining NICs are fairly static apart from a small amount of traffic.

The number of bytes sent across the Mirror NIC indicate that the network is being used to transport the primary database transactions across to the Secondary(s).

As mentioned previously, before configuring your AlwaysOn Availability group to segregate mirror traffic, you must ensure that the dedicated network has the necessary bandwidth to support the load. This not only includes all the cabling and switches but also the server network cards too. Ensure you test thoroughly and most importantly employ premium hardware to support the intended load.

As always, post any questions in the discussion and I'll help all I can.

 

This article is part of the Stairway to AlwaysOn Stairway

Sign up to our RSS feed and get notified as soon as we publish a new level in the Stairway! Rss

Total article views: 1571 | Views in the last 30 days: 13
 
Related Articles
ARTICLE

Step by Step Configuring a Dedicated Network for Availability Group

In this article, I will show a procedure to isolate the replication traffic from the public network....

FORUM

Changind Database Mirroring Endpoints

Changing Database Mirroring Endpoints without resetting db mirroring

BLOG

Step by Step Configuring a Dedicated Network for Availability Group

One of my clients came up with the requirement to isolate the replication traffic from the public ne...

BLOG

Change Availability Group Endpoint IP

I had someone email me and ask how they could change the IP address on their Availability Group Endp...

BLOG

Change Availability Group Endpoint Port

Let’s say you have a port conflict and need to change the port on your Availability Group endpoint. ...

Tags
always on    
stairway series    
 
Contribute