Multi-homed SQL Server with High Availability Groups

Question

We have two servers (SQL-ATL01, SQL-ATL02) that make up a Failover Cluster, each running as part of a SQL Server High Availability Group (HAG). Each server has two network cards. One is a 10Gbit card that is directly connected to the other server and is used for Synchronizing the HAG on a 192.168.99.x subnet. The other is a 1Gbit card that is used to connect the DB servers to a switch to communicate with the application servers on a 10.0.0.x subnet. The Listener is pointed to the 192.168.99.x subnet.

We want to add a third server (SQL-NYC01) in another physical location to the cluster and run it as an Async replica part of the HAG, but the VPN only routes traffic on the subnet on the 1Gbit network.

Is there any way to set up the Failover Cluster and High Availability Group to tell it:

• Send synchronous replica traffic for SQL-ATL01 <--> SQL-ATL02 over 192.168.99.x
• Send asynchronous replica traffic for (SQL-ATL01, SQL-ATL02) <--> SQL-NYC01 over 10.0.0.x

Or do we have to have all replica traffic going in and out on the same IP address/subnet?

Answer 1

There is a way to do it, otherwise this would be a major drawback.

Before starting you should create backups and restore DBs so you can use Initial data synchronization type will be only JOIN ONLY (since you have only 1Gbit node).

join only - is to start synchronization if you already created backups and restored on all secondary replicas

To make my life easier I'll presume some IP addresses which you can easily adapt:

sync node: SQL-ATL01 192.168.99.1
sync node: SQL-ATL02 192.168.99.2
async node: SQL-NYC01 10.0.0.10

1. Go to the SQL Server properties (on SQL-ATL01) and right click on the sql server service and click on properties - there find a tab AlwaysOn High Availability and check the checbox Enable AlwaysOn Availability Groups (you will see the Windows fail over cluster name above. After changing this property you need to restart the SQL Services. Do that for both other replicas SQL-ATL02 and SQL-NYC01.
2. Now start SQL Server management studio and connect to the primary replica which is probably SQL-ATL01 (based on its name). Right-click on AlwaysOn High Availability and select the Wizard (the first option). You will the wizard window where you will see the overview what can you do - you can click next button.
3. First you will specify the Availability group name e.g. SQLAG (AG as availability group)
4. Next you need to specify the databases which will be included. Select the one you want.

5. Now getting to the core (Specify Replicas). Here we will work with tabs Replicas and Listener.

   Starting with Replicas you need to add your Replicas (I'm only writing the important/changed columns):

╔═══╦═══════════════════╦═════════════════╦═════════════════════╦══════════════════════╗
║   ║ Server Instance   ║ Initial Role    ║ Automatic Failover  ║   Synchronous commit ║
╠═══╬═══════════════════╬═════════════════╬═════════════════════╬══════════════════════╣
║ 1 ║ SQL-ATL01         ║ Primary         ║ Checked             ║ Checked              ║
║ 2 ║ SQL-ATL02         ║ Secondary       ║ Checked             ║ Checked              ║
║ 3 ║ SQL-NYC01         ║ Secondary       ║ Unchecked           ║ Unchecked            ║
╚═══╩═══════════════════╩═════════════════╩═════════════════════╩══════════════════════╝

If you want SQL-NYC01 to be a failover or sync you have to check the checkboxes here.

6. Now to the tab Listener Click on Create an availability group listener. You have to specify the DNS name for the Listener for example SQLAGListener, select port (default is 1433) and Network mode -> Static IP.

Now click on button add where you select your subnets and IP address:

╔═══╦═════════════════╦═════════════════════════════╗
║   ║ Subnet          ║ IP Address                  ║
╠═══╬═════════════════╬═════════════════════════════╣
║ 1 ║ 192.168.99.0/24 ║ 192.168.99.1 192.168.99.2   ║
║ 2 ║ 10.0.0.0/24     ║ 10.0.0.10                   ║
╚═══╩═════════════════╩═════════════════════════════╝

Then click next.

7. Initial Data Synchronization As stated at beginning since you have 1Gbps node I would recommend going with Join only.

The options are:

Full - is to start the synchronization after creating and restoring the backups
Join only - is to start synchronization if you already created backups and restored on all secondary replicas
Skip initial data synchronization - this is used if you still need to create a backup and restore to the replicas

8. Next is validation which will check all your settings. Click next to proceed to summary page
9. The last is Results page where you will see all the checks like configuring endpoints, Starting the 'AlwaysOn_health', Creating availability group SQLAG, Create Availability Group Listener SQLAGListener, etc.

Next step VALIDATION

You should see in your SQL Server Management studio at each node AlwaysOn HA each node of SQLAG (SQL-ATL01 (primary), SQL-ATL02 (secondary), SQL-NYC01 (secondary)).

If you expand it at e.g. SQL-ATL01 you should see all replicas in Availability Replicas and the selected databases in Availability Databases and the configured listener (SQLAGListener) in the Availability Group Listeners.

You could also check the Failover Cluster Manager.

Where you would see the configured listener SQLAGListener: The Primary node 192.168.99.1 will be shown as online and the others as offline.

To see the current status of the nodes you can also right-click on SQLAG(Primary) and show dashboard. This can be done for every node and can be used for testing/viewing the current state if you need to do so.

Edit

You should also test the configuration if you did not miss anything.