question

StephenHuo-1450 avatar image
0 Votes"
StephenHuo-1450 asked ·

Copy Acitivity Failed while copying data from SQL Server in Azure VM to Azure SQL Database

Hi,

We recently hit the copy acaitivty failed couple times when copying data from SQL Server in Azure VM to Azure SQL Database, also same issue happen when copying data from Azure SQL Database to another Azure SQL Database. Could you help to provide some suggestions? Thanks.
Here’s more details:
We got several pipeline running at a regular time to loading the data once a day. There’s a for each loop in the pipeline to load all the table at the same time.
The failed happen while copying data for one or two medium size table (between 200MB to 500MB), as indicated in the error log and performance snapshot. It’s not happening every day, which make it difficult to recreate the issue manually. Same issue happen when copy data between different Azure SQL Database. Any idea would be helpful.

Source: SQL Server in Azure VM or Azure SQL Database (elastic pool)
Sink: Azure SQL Database (50 DTU)


Error Log:
Error code2200
Troubleshooting guide
Failure typeUser configuration issue
DetailsErrorCode=SqlBatchWriteTransactionFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL transaction commits failed,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.),Source=.Net SqlClient Data Provider,SqlErrorNumber=121,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=121,State=0,Message=A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.),},],''Type=System.ComponentModel.Win32Exception,Message=The semaphore timeout period has expired,Source=,'

72741-screenshot-2021-02-27-at-002609.png


azure-data-factory
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @StephenHuo-1450,

Welcome to Microsoft Q&A forum and thanks for your query.

As called out in the ADF Troubleshooting doc, could you please try updating the SQL-linked service connection string with a connection timeout value that's equal to or greater than 120 and see if that helps to resolve the issue.

73089-image.png

Hope this helps. Let us know how it goes.

Thank you


1 Vote 1 ·
image.png (43.0 KiB)

1 Answer

KranthiPakala-MSFT avatar image
0 Votes"
KranthiPakala-MSFT answered ·

Hi @StephenHuo-1450,

Welcome to Microsoft Q&A forum and thanks for your query.

As called out in the ADF Troubleshooting doc, could you please try updating the SQL-linked service connection string with a connection timeout value that's equal to or greater than 120 and see if that helps to resolve the issue.

73089-image.png

Hope this helps. Let us know how it goes.

Thank you


· 4 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @StephenHuo-1450,

Just checking in to see if the above suggestion was helpful. And, if you have any further query do let us know.

0 Votes 0 ·

Thanks for the efforts @KranthiPakala-MSFT ! Yep I noticed that the current connection string in the linked service is missing the connection timeout parameter, which it will be set as default (30 seconds). Therefore I followed your suggestion and raised it up to 300 to lower down the timeout frequency:

"connectionString": "integrated security=False;encrypt=True;connection timeout=300;data source=;initial catalog=",

Another change that I made is to change the for each loop for the table loading, in that case we just process one copy activity at a time. Then the copy process runs 2-3 faster than before, which leads to less chances hitting timeout issue,

After we applied the above changes, so far no timeout issue appeared :)

Thanks for the help!

Regards

0 Votes 0 ·

Hi @StephenHuo-1450, Glad to know that the above information was helpful to resolve your issue and thanks for sharing additional insights. Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.

Have a good day!

Thank you

0 Votes 0 ·

Done, thanks :)

0 Votes 0 ·