Share via

if i deploy Always on availability group in multi subnet do we need to mention multisubnetfailover=true as compulsary

SaikrishnaE 51 Reputation points
2024-02-09T12:24:19.0166667+00:00

we have an AX application that has Databases configured with the Always On availability group. we have deployed the Always On availability group in a single subnet, currently, we are facing issues with the connections through the Load balancer to the listener with the connection timed out error. We would like to move the AG to the multi-subnet deployment. But our application doesn't support the parameter "Multisubnetfailover=True", is it mandatory to keep the "Multisubnetfailover=True" in the connection string. What happens if we don't use it, are the connections going to fail? please let us know the limitations also please let us know if we can move forward without "Multisubnetfailover=True".

Thanks in Advance

SQL Server on Azure Virtual Machines
Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,945 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ben Miller (DBAduck) 951 Reputation points
    2024-02-09T17:50:14.4666667+00:00

    If you are in a multi-subnet scenario and you do not use Multisubnetfailover=true and you have not changed the Cluster Parameter on the Name resource to RegisterAllProvidersIP = 0 then it is possible for the client to get the IP that is not active and time out. With RegisterAllProvidersIP = 1 (which is default) both IP addresses are registered in DNS and only 1 is active at a time, so it could get the inactive DNS record and time out.

    If you have RegisterAllProvidersIP = 0 then in DNS there is only the active one and it should work fine. The side effect of that is that when you fail over it will register the new IP of the other subnet and the DNS TTL will take over from there. So if you do this, you may need to lower the TTL for the DNS record by using the HostRecordTTL = 5 (this is seconds). You can choose a longer HostRecordTTL if you would like, but it is important to understand expectations of how fast the clients will connect to the new replica after failover.

    So without the Multisubnetfailover=True you can see some interesting things happen with connections. Any more questions about this, feel free.

    0 comments
  2. Javier Villegas 895 Reputation points MVP
    2024-02-09T20:08:11.6666667+00:00

    Hello If you have an AG Listener properly configured and your application is using it in the connection string you don't have to use any parameters Regards Javier

    0 comments
  3. ZoeHui-MSFT 33,941 Reputation points
    2024-02-12T02:08:58.2533333+00:00

    Hi @SaikrishnaE,

    The MultiSubnetFailover connection option should be set to True even if the availability group only spans a single subnet. This allows you to preconfigure new clients to support future spanning of subnets without any need for future client connection string changes and also optimizes failover performance for single subnet failovers. While the MultiSubnetFailover connection option is not required, it does provide the benefit of a faster subnet failover. This is because the client driver will attempt to open up a TCP socket for each IP address in parallel associated with the availability group. The client driver will wait for the first IP to respond with success and once it does, will then use it for the connection.

    See Connect to an Always On availability group listener

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.