Post reply

BCP Login timeout when running on a 2014 "Always on Availability groups" with multisubnets

  • June 19, 2018 at 4:57 am

    Go to Answer

    #351831

    Hi Experts,

    I've encountered a login timeout issue when running a BCP command on a SQL 2014 "AOAG" multisubnets environment.
    This issue was initially encountered with all the connections to the Database, as some of the connections attempts succeeded and some failed due to timeouts.

    The Application connections issue was resolved by adding the MultiSubnetFailover parameter to the connection strings to allow them to quickly resolve the correct replica to work with. (we're only interested in the primary replica)

    Is there a way to resolve this for BCP as well ?

    The only workaround I can think of is adding the new timeout parameter in bcp 12 (-l) to allow it enough time to resolve the right replica.
    https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/sql-server-multi-subnet-clustering-sql-server?view=sql-server-2017#DNS
     Any more ideas ?

    Update: 
    Adding the Login timeout parameter to the BCP command resolved the issue by allowing the BCP to iterate through the replicas until the primary is found.
    Still, I would like to find an equivalent to the MultiSubnetFailover for the BCP utility to allow fast Primary replica resolution.
    Should anyone know of such an option, I'd love to know.

  • May 6, 2020 at 1:08 pm

    Answer

    #3749299

    Hello John,

    I was stuck with same for couple of days and was not able to find anything over internet.

    However, I found one option to change the settings to use the multisubnet failover option to YES and it worked for me.

    All you have to do is to pass the option ;multisubnetfailover=yes in command line after the listener name as below:

    c:\Windows\System32> bcp "SELECT 1" queryout c:\temp_log\bcp12.log -S ListenerName;multisubnetfailover=yes   -T

    You can find the same at below link:

    https://accidentalsqldba.blogspot.com/2020/05/bcp-with-multisubnetfailover-option.html

    Please let me know if this works for anyone who is reading this 🙂

    Thank You!

    Vimal Prajapati

     

     

     

  • October 12, 2020 at 6:59 am

    #3796231

    Hello Vimal!

    I passed your comment to the relevant DBA and he confirmed it works for him.

    Thank you so much for taking the time and posting your advice.

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

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