Post reply

Multi subnet AG - Listener request time out

  • February 9, 2022 at 6:04 am

    #3981443

    Hi,

    DR setup combination of 2 node windows cluster and Alwayson availability group,  AG listener configured multiple IP address.

    1. First time pinging Listener name - Request time out. ( IP showing remote site )
    2.  Second time pinging Listener name - pinging success ( IP showing primary site) - kept windows cluster and AG resources are primary role in primary site only. so my question is - Fist time pinging listener name by default not coming Primary site IP address? So does it require DNS registration at AD level with multiple IP address?

    Thanks

  • February 9, 2022 at 6:39 am

    #3981449

    You need to use the connection option “MultiSubnetFailover=True” in your apps and management studio.

    By default all IPs are registered via the cluster.  That way you get quicker re connectivity on failover.

    If your apps are old and the drivers the use ancient (OLEDB or anything dotnet 3.5 or below) then you need to set “RegisterAllIpProviders=0” at the cluster level.

    That means though your reliant on DNS replication when failing over, so also be sure to reduce the TTL setting of the listener IP too.

  • February 9, 2022 at 9:40 am

    #3981473

    Hi

    Thanks for your reply

    windows 2016 STD edition, SQL 2016 Enterprise edition.

    1. MultiSubnetFailover=True this switch already mentioned application connection string.
    2.  Application using 4 .2 version dotnet version - checked with apps team.
    3. RegisterAllIpProviders=1 - manually done thru PowerShell for register all IPs because if resources failover/switchover to remote site so immediately get pinging remote site IP address. if RegisterAllIpProviders=0 does it mean register only one IP (primary site)? am i right. if set 0 value what will happen if failover AG to remote site?
    4. TTL value set to 120 from default value.

    Thnaks.

  • February 9, 2022 at 9:54 am

    #3981476

    So yeah you have done everything right then.

    No issues at all.

     

    You’re facing a limitation on ping where it found the inactive IP in DNS first as it round robins the IPs.

  • February 9, 2022 at 9:56 am

    #3981477

    Register all IP providers = 0 is exactly that, you only register the active IP in DNS.

    Then when failover happens the old IP is removed and the new IP is added, then it need to replicate.

    So you could be up to a couple of minutes or slightly longer to reconnect if TTL = 120 and Register All IPs = 0

  • February 10, 2022 at 6:47 am

    #3981646

    Thank you ANT_Green.

    How do we maintain SQL jobs in secondary replica? how to set jobs execute primary and secondary AG when failover/switchover two nodes.

    Thanks

  • February 10, 2022 at 5:34 pm

    #3981752

    You need to use the inbuilt function below wrapping your job step command in the function.

     

    https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-hadr-is-primary-replica-transact-sql?view=sql-server-ver15

     

  • February 10, 2022 at 5:58 pm

    #3981753

    SQL Galaxy wrote:

    Thank you ANT_Green.

    How do we maintain SQL jobs in secondary replica? how to set jobs execute primary and secondary AG when failover/switchover two nodes.

    Thanks

    What types of jobs?  If it is maintenance, such as backups, updating stats, and so forth, a tool such as Ola Hallengrens maintenance solution is your friend.  https://ola.hallengren.com/ It will automatically determine if a database is the primary replica, and only execute if it is.

    Other jobs, you can test to see what server may be the primary by querying the sys.dm_hadr_availability_group_states table

    SELECT
    HAGS.primary_replica
    FROM sys.dm_hadr_availability_group_states HAGS
    INNER JOIN sys.availability_groups AG ON HAGS.group_id = AG.group_id

    Depending upon the job, in the first step you could test if it's primary, and go to the next step.  If it's not, throw an error and end the job

    You could add an IF statement, and only execute code if it's primary.

    You could use SQL Agent alerts, and when triggered, execute a job that disables jobs on the primary.  That one is a little tricky though.

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 8 posts - 1 through 7 (of 7 total)

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