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 Always On Availability group to have a high volume of transactional traffic.

In a busy Always On 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 Always On 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 Always On group. For this level, each node has 3 network cards, 1 for each of the following network segments

As my Always On 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 Always On 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.