This article on SQL Server Always On Listeners includes an overview and various connection configurations for specifying MultiSubnetFailover including SSMS, ODBC, ADO.NET, as well as MultiSubnetFailover limitations and how to connect to all IP addresses via RegisterAllProvidersIP.
SQL Server Always On Availability Groups provides a HADR (high availability and disaster recovery) solution with multiple replicas. We can create multiple secondary replicas with a combination of synchronous and asynchronous data commit.
Overview of SQL Server Always On Listeners
Suppose we have two replicas using synchronous data commit mode. We can do read-write operations on the primary replica only. In the application configuration, we can use the Primary replica instance name. It has a drawback in that if failover happens and the secondary replica takes over the role as primary, all user connections need to repoint to the new primary replica after failover. It is not an ideal scenario to change the application connection string each time after a failover.
In SQL Server Always On, we can define a SQL Server Always On Availability listener for all user connections. A virtual network name always points to the primary replica. The client application does not need to know the underlying configuration of Always On replica.
In the following screenshot, we configured a SQL listener on a three node replica.
To configure a Listener in SQL Server Always on Availability Group, expand the Availability Group Listener node in SSMS. Then click on Add Listener.
Provide the following information in the Add Listener wizard.
- Unique virtual network name in DNS
- Port for SQL Listener. We can use the default SQL port 1433 as well; however, we need to ensure that no other services, on the cluster node, should use this port as it can cause a port conflict. Ideally, we should use a different port for SQL listener. Applications need to use this port in their connection string. In the following screenshot, we can see port 5123 is configured
Once we configure a SQL listener in a SQL Server Always On Availability Group, it also becomes a cluster resource. You can open the failover cluster manager and view Roles. In the Roles, you can see a virtual network name (listener name) along with virtual IP.
Multi-Subnet SQL Server Always On Availability Groups
Let’s consider the case of multi-subnet Always On Availability Groups. In the case of a multi-subnet, we need to use multiple IPs for the SQL listener. We need to configure one virtual IP for each subnet. Therefore, if we have a configuration for DC-DR Always on replicas, we can have one virtual IP for DC and one virtual IP for DR.
Once a client connects via DNS to resolve the virtual network name, it returns all available IP addresses. It tries to connect with each IP address and connect to the available (DC or DR) IP address.
Let’s say you have configured SQL Listener with two IP address, one for DC and another for DR subnet. Suddenly due to some network issues, the Availability group fails over to the Secondary replica in DR. Previously in the failover cluster manager, the DC Listener IP address was online but after failover the DR Listener IP address came online. The application tries to connect the primary replica using the Listener. It connects with DNS to resolve its virtual network name and gets a list of multiple IP addresses in return. The application tries to connect with the first IP address and could not connect to it. Once it gets a timeout issue, it checks for another IP address, and the connection is established. It is a trial and error approach. If we have multiple subnets, the application connection might get delayed further.
We can use the configuration MultiSubnetFailover=True, in the connection string, to resolve this issue. Once we enable this parameter, the Application tries to connect both IP address simultaneously. Whichever IP address connection is successful, the application connects with the Primary replica using that IP address and routes read-write queries on that replica.
Let’s graphically understand MultiSubnetFailover in SQL Server Always On Availability Groups. In the following image, we have a straightforward two-node SQL Server Always on Cluster. We have both the nodes in a different subnet. In SQL Listener, we have two virtual IP address mapped with a SQL Listener name.
- Step 1: Once the application tries to connect to the database using a SQL listener, it connects with DNS and asks for the IP address of it.
- Step 2: DNS gives all virtual IP address mapped with the SQL Listener. In this example, DNS returns two IP addresses
- Step 3 and 4: Only one virtual IP is online at any given time. The application tries to connect with IP Address 1, but it couldn’t connect to the Primary replica because the IP address 1 is offline
- Step 5: Once a connection timeout (default 12 seconds) occurs, the same process happens with IP Address 2. IP Address 2 points to the primary replica and database connection are successful
Now let’s view this example again with MultiSubnetFailover= True in the Connection string. If you want to connect the database with SSMS, go to Additional Connection parameters and specify MultiSubnetFailover= True.
Specifying MultiSubnetFailover in SSMS Connection
Specifying MultiSubnetFailover in ODBC Connection
We can also specify specifying MultiSubnetFailover in the ODBC Connection as well as shown in the following screenshot. By default, this option is not turned on.
Specifying MultiSubnetFailover in ADO.NET provider Connection
We can specify MultiSubnetFailover in the ADO.Net provider connection string as per following connection string.
Server=tcp:SQListenerName,Port;Database=TestDB;IntegratedSecurity=SSPI; MultiSubnetFailover=True
We can understand the connection process with MultiSubnetFailover =True using following steps.
- Step 1: Once the application tries to connect to the database using a SQL listener, it connects with DNS and asks for the IP address of it
- Step 2: DNS gives all virtual IP address mapped with a SQL Listener. In this example, DNS returns two IP addresses
- Step 3: In MultiSubnetFailover= True, it tries to connect with all IP addresses returned by DNS in parallel. It does not wait for the timeout to occur from an IP address
- Step 4: Once a connection is established with one IP address, the application can connect to the database
We should enable the MultiSubnetFailover for SQL Server Availability Group for a single subnet availability group as well. It helps to facilitate application behavior in case we later expand availability group across multiple subnets.
Limitations of MultiSubnetFailover
- It does not support hostnames with more than 64 IP addresses
- It only supports TCP protocol
- It does not connect to mirrored SQL Server instances
- It does not support SQL named instances. We can connect named SQL instances using a SQL listener
RegisterAllProvidersIP configuration in SQL Server Always On Availability Groups
Most client tools support multi-subnet behavior by providing the option to enable MultiSubnetFailover true. However, by default, the application tries to connect with multiple virtual IP addresses in a serial mode only. We have to explicitly define the multi-subnet method to go with parallel processing of connections.
Once we configure MultiSubnetFailover in SQL Server cluster, it sets the RegisterAllProvidesIP value to one.
If the RegisterAllProvidesIP value is 1, then all IP’s are registered and available for application connectivity. If the application does not support MultiSubnetFailover, we can still avoid connections to all IP addresses.
To check the existing value of RegisterAllProvidesIP, open PowerShell with administrative permission and run the following command.
|
1 |
>Get-ClusterResource "Cluster Resource Name" | Get-ClusterParameter |
In the output, check the value of RegisterAllProvidesIP as shown in the following screenshot.
We can switch the value of RegisterAllProvidesIP to 0. Once it is set, only the IP of the active node is registered. Once application connects to DNS for the listener IP address, it gets the active node IP address, and the connection is successful. It eliminates the timeout issue that we may face in case of multiple IP addresses.
To change the RegisterAllProvidesIP, execute the following command in administrative PowerShell.
|
1 |
>Get-ClusterResource “Cluster Resource Name”| Set-ClusterParameter RegisterAllProvidersIP 0 |
If we do not know the Cluster Resource Name, execute the command Get-ClusterResource to get a list of all available cluster resources.
Once we execute the command, we need to restart the cluster role. We can either take the cluster role offline and online. We might be using the MultiSubnetfailover=True option in the application level connection string; however, it is recommended to set RegisterAllProvidesIP value to 0 as well.
Conclusion
In this article, we explored Always On listeners in SQL Server Always On Availability Groups along with MultiSubnetFailover and RegisterAllProvidesIP configurations. If you had comments or questions, feel free to leave them in the comments below
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023