Wednesday, May 6, 2020

BCP with MultiSubNetFailover option

Recently, I was stuck with one issue where I was getting a login timeout error when running a BCP command on a SQL 2016 "AlwaysOnAvailabilityGroup" with multisubnet environment.

Our application which was using the JDBC driver to connect to database was running fine.

But when we were trying to connect the database with bcp command it was getting timeout.

I found one workaround at below link which says to use the -l parameter. It will extend the timeout value and will start working. This was taking time as it was resolving the IP's in serial manner.
The above option was working fine but was slow. So, I tried some options with bcp command line and found one which worked in my case.

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

I changed the connections in my case and started working fine after that.

Please let me know if this works for you :)

Thank You!
Vimal

No comments:

Post a Comment