SQL Server Multi-Subnet Clustering (SQL Server)

Artikkeli
03/03/2023

Applies to: SQL Server

A SQL Server multi-subnet failover cluster is a configuration where each failover cluster node is connected to a different subnet or different set of subnets. These subnets can be in the same location or in geographically dispersed sites. Clustering across geographically dispersed sites is sometimes referred to as stretch clusters. As there is no shared storage that all the nodes can access, data should be replicated between the data storage on the multiple subnets. With data replication, there is more than one copy of the data available. Therefore, a multi-subnet failover cluster provides a disaster recovery solution in addition to high availability.

SQL Server Multi-Subnet Failover Cluster (Two-Nodes, Two-Subnets)

The following illustration represents a two node, two subnet failover cluster instance (FCI) in SQL Server.

Multi-Subnet Architecture with MultiSubnetFailover

Multi-Subnet Failover Cluster Instance Configurations

The following are some examples of SQL Server FCIs that use multiple subnets:

SQL Server FCI SQLCLUST1 includes Node1 and Node2. Node1 is connected to Subnet1. Node2 is connected to Subnet2. SQL Server Setup sees this configuration as a multi-subnet cluster and sets the IP address resource dependency to OR.
SQL Server FCI SQLCLUST1 includes Node1, Node2, and Node3. Node1 and Node2 are connected to Subnet1. Node 3 is connected to Subnet2. SQL Server Setup sees this configuration as a multi-subnet cluster and sets the IP address resource dependency to OR. Because Node1 and Node2 are on the same subnet, this configuration provides additional local high availability.
SQL Server FCI SQLCLUST1 includes Node1 and Node2. Node1 is on Subnet1. Node2 is on Subnet1 and Subnet2. SQL Server Setup sees this configuration as a multi-subnet cluster and sets the IP address resource dependency to OR.
SQL Server FCI SQLCLUST1 includes Node1 and Node2. Node1 is connected to Subnet1 and Subnet2. Node2 is also connected to Subnet1 and Subnet2. The IP address resource dependency is set to AND by SQL Server Setup.

Note

This configuration is not considered as a multi-subnet failover cluster configuration because the clustered nodes are on the same set of subnets.

IP Address Resource Considerations

In a multi-subnet failover cluster configuration, the IP addresses are not owned by all the nodes in the failover cluster, and may not be all online during SQL Server startup. Beginning in SQL Server 2012 (11.x), you can set the IP address resource dependency to OR. This enables SQL Server to be online when there is at least one valid IP address that it can bind to.

Note

In the SQL Server versions earlier than SQL Server 2012 (11.x), a stretch V-LAN technology was used in multi-site cluster configurations to expose a single IP address for failover across sites. With the new capability of SQL Server to cluster nodes across different subnets, you can now configure SQL Server failover clusters across multiple sites without implementing the stretch V-LAN technology.

IP Address Resource OR Dependency Considerations

You may want to consider the following failover behavior if you set the IP address resource dependency is set to OR:

When there is a failure of one of the IP addresses on the node that currently owns the SQL Server cluster resource group, a failover is not triggered automatically until all the IP addresses valid on that node fail.
When a failover occurs, SQL Server will come online if it can bind to at least one IP address that is valid on the current node. The IP addresses that did not bind to SQL Server at startup will be listed in the error log.

When a SQL Server FCI is installed side-by-side with a standalone instance of the SQL Server Database Engine, take care to avoid TCP port number conflicts on the IP addresses. Conflicts usually occur when two instances of the Database Engine are both configured to use the default TCP port (1433). To avoid conflicts, configure one instance to use a non-default fixed port. Configuring a fixed port is usually easiest on the standalone instance. Configuring the Database Engine to use different ports will prevent an unexpected IP Address/TCP port conflict that blocks an instance startup when a SQL Server FCI fails to the standby node.

Client Recovery Latency During Failover

A multi-subnet FCI by default enables the RegisterAllProvidersIP cluster resource for its network name. In a multi-subnet configuration, both the online and offline IP addresses of the network name will be registered at the DNS server. The client application then retrieves all registered IP addresses from the DNS server and attempts to connect to the addresses either in order or in parallel. This means that client recovery time in multi-subnet failovers no longer depend on DNS update latencies. By default, the client tries the IP addresses in order. When the client uses the new optional MultiSubnetFailover=True parameter in its connection string, it will instead try the IP addresses simultaneously and connects to the first server that responds. This can help minimize the client recovery latency when failovers occur. For more information, see Always On Client Connectivity (SQL Server) and Create or Configure an Availability Group Listener (SQL Server).

With legacy client libraries or third party data providers, you cannot use the MultiSubnetFailover parameter in your connection string. To help ensure that your client application works optimally with multi-subnet FCI in SQL Server, try to adjust the connection timeout in the client connection string by 21 seconds for each additional IP address. This ensures that the client's reconnection attempt does not timeout before it is able to cycle through all IP addresses in your multi-subnet FCI.

The default client connection time-out period for SQL Server Management Studio and sqlcmd is 15 seconds.