Post reply

multi-subnet failover sql cluster

  • November 12, 2019 at 9:46 am

    #3696975

    Hi All,

    Have a question related for multi-subnet AG.

    Trying to understand behaviour of Listerner in a multisubnet AG Cluster.

    Recently, in our org, they migrated on-prem 2-node AG cluster to Azure IaaS 3-node AG cluster. Win Server 2016 + SQL Server 2016 EE.

    It is a multi-subnet SQL cluster. In one subnet there are 2 nodes which is configured as Synchronous replicas and in subnet 2 used as DR Site, which has 1 node configured for Asynchronous manual failover.

    Since, it is a multi-subnet AG, the listerner has 2 IP's and in Failover cluster manager , we see 1 IP Address is ONLINE at any point of time and other IP Adrees is OFFLINE.

    In ssms and application connection strings, we are using this option MultiSubnetFailover=True while trying to make SQL connections. This is working fine for sometime, but in recent times, jobs are trying to connect to the DR site instead of main site and getting request time out connectivity errors. Due to this, some jobs are failing on production.

    Now, my question is, while we try to make a connection to the sql server using LISTENER , WHY application is trying to connect to DR Site. We expect the connections to connect to the main site/subnet whose IP address is online in Failover cluster manager.

    All I wanted to know how the DNS probing happens when we try to connect to the Listener? What happens in the background before client can make a successful connection to sql server using Listerner when using multi-subnet failover?

    Is there any settings needs to changed or additional settings to be made at Cluster Level ?

    Thanks,

    Sam

  • November 12, 2019 at 8:49 pm

    #3697162

    Looks like classic issue with multi subnet AG Cluster, check this article it explains and provide solutions. In short, stuff from jobs must also use multisubnet option if applicable or change the way how IP is registered in DNS and set lower TTL.

    https://techcommunity.microsoft.com/t5/SQL-Server-Support/Connection-Timeouts-in-Multi-subnet-Availability-Group/ba-p/318334

     

  • November 18, 2019 at 4:02 pm

    #3698575

    Does the cluster have the RegisterAllProvidersIP parameter configured?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply