SQL Server – Multi-Subnet AlwaysOn: Is Routing and Remote Access Always Required?

availability-groupsNetworksql server

Context

I'm setting up a 2-node AlwaysOn Availability Group (SQL Server 2017 Enterprise) with the following setup:

Existing DC+DNS (One NIC: 10.20.11.1/255.255.254.0)
New AG-Node1 (One NIC: 10.20.11.2/255.255.254.0)
New AG-Node2 (One NIC: 10.22.11.1/255.255.254.0)

I've read that this would be considered as an "AlwaysOn Multi-subnet" scenario (since nodes belong to different subnets).

Question:

Do I really have to treat this as an "AlwaysOn AG Multi subnet" even when all required routing is already implemented at networking device level and computers can all reach each other?

I've read that Routing and Remote Access is supposed to be deployed and configured but I do not get why this would be necessary since the routing is already implemented at networking device level.

Best Answer

Do I really have to treat this as an "AlwaysOn AG Multi subnet" even when all required routing is already implemented at networking device level and computers can all reach each other?

Yes. SQL Server isn't doing any "routing" at the network level, it's not a layer-3 device... However, if you don't treat it as a "multi-subnet cluster" then you can get yourself into some time-out causing situations. While the situations are easily rectifiable, why not save the trouble and set everything up the first time, then not have to go back and change things?

I've read that Routing and Remote Access is supposed to be deployed and configured but I do not get why this would be necessary since the routing is already implemented at networking device level.

I don't know why someone is telling you to setup RRAS... unless it's for some local repro environment, such as the one on my laptop that doesn't have any routing devices as it's all internal virtual machines. This doesn't make any sense unless you're using RRAS as the routing device (software router).

Either way, this is all network design and has nothing specifically to do with SQL Server.

Related Solutions

SQL Server – AlwaysOn AG and DTC with Failover

Elijah. There's two separate questions here:

1. Is DTC supported with AlwaysOn Availability Groups?

You're using SQL Server 2012, and according to Microsoft's Documentation, that answer is no. I totally understand that you want to try it anyway, but keep in mind that you're now putting something into production that Microsoft simply will not support, AND you're using two separate niche features together (AGs and DTC). If anything whatsoever goes wrong, you're going to be in a world of hurt. This just isn't something I'd ever even think about trying in production.

Keep in mind that if your managers find out that you deployed something Microsoft specifically says in big letters, "YOU CAN'T DO THIS," and you have any kind of outage where you have to call Microsoft for support, you're going to have some ugly explaining to do.

Technically, DTC is supported starting with SQL Server 2016 SP2 and later, but it just means that you can pick which database loses data on failover, and the application has no idea data was permanently lost. That's not what a normal database administrator would call DTC support.

2. How should DTC be configured in a multi-node, multi-subnet cluster?

Read Allan Hirt's post on configuring DTC with multiple instances of SQL Server in a cluster, and make sure to read all of the links in the post as well.

AlwaysOn – Availability Group Listener in a Multi-Subnet Cluster

To flesh out the accepted answer a little bit for anyone looking for code examples and order of operations, I had to:

First create the Availability Group on the primary node
Add a static subnet IP to the AG listener on the primary node.
Connect to the secondary node and JOIN it to the Availability Group
Connect BACK to the secondary node and add the second static subnet IP address (which belongs to the subnet of the secondary node) to the AG listener.

Here is a partial sample of the code showing the steps above (obviously with dummy IPs and node names):

:Connect PrimaryAGInstName

USE [master]

GO

CREATE AVAILABILITY GROUP [PrimaryAGInstName]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE [DBName]
REPLICA ON N'PrimaryAGInstName' WITH (ENDPOINT_URL = N'TCP://PrimaryAGInstName.Domain.Info:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
    N'SecondaryAGInstName' WITH (ENDPOINT_URL = N'TCP://SecondaryAGInstName.Domain.Info:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));

GO

:Connect PrimaryAGInstName

USE [master]

GO

ALTER AVAILABILITY GROUP [PrimaryAGInstName]
ADD LISTENER N'AG_Listener_Name' (
WITH IP
((N'12.345.67.8', N'255.255.248.0')
)
, PORT=1919);

GO

:Connect SecondaryAGInstName

USE [master]

GO

ALTER AVAILABILITY GROUP [PrimaryAGInstName] JOIN;

GO


:Connect PrimaryAGInstName

USE [master]

GO

ALTER AVAILABILITY GROUP [PrimaryAGInstName]
ADD LISTENER N'AG_Listener_Name' (
WITH IP
((N'12.567.89.0', N'255.255.248.0')
)
, PORT=1919);
GO