none
Multi-subnet AG cluster questions(s) RRS feed

  • Question

  • Hi All,

    Have a question related for multi-subnet AG.
    Trying to understand behavior 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

    Tuesday, November 12, 2019 9:46 AM

All replies

  • Hi Sam,

    Could you please try to set the value of the parameter RegisterAllProvidersIP to 1? With this setting, all subnets' IP addresses will be attached to the listener name. 

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, November 13, 2019 10:06 AM
  • When the 'RegisterAllProvidersIP ' is set to 0, only one IP resource is able to be online.

    For your question [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. ]

    It may be due to cache issue. You can clean the cache as a workaround or reduce HostRecordTTL.

    If your application supports 'MultiSubnetFailove' keyword, please change the RegisterAllProvidersIP to 1.


    https://sqlserver.code.blog/


    Friday, November 15, 2019 9:01 PM