ADF Copy from SQL Server to Storage failure

Question

ADF Copy from SQL Server to Storage failure

Matthew Pruett 96

I'm running into an unusual issue. I have a pipeline set to extract data from SQL Server on-prem to a Azure Blob storage. I have several similar pipelines but only this one fails for this particular server.

When I view the event log on the server at the time of failure, I see several errors.

The first error:

WritePartAsync hit storage exception at stream position 10747904, failure: 'Type=Microsoft.WindowsAzure.Storage.StorageException,Message=Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host.,Source=Microsoft.WindowsAzure.Storage,StackTrace= at Microsoft.WindowsAzure.Storage.Core.Util.StorageAsyncResult1.End() at Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob.EndPutBlock(IAsyncResult asyncResult) at Microsoft.WindowsAzure.Storage.Core.Util.AsyncExtensions.<>c__DisplayClass7.<CreateCallbackVoid>b__5(IAsyncResult ar) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.DataTransfer.Runtime.AzureBlobMultipartRandomWritable.<WritePartAsync>d__18.MoveNext(),''Type=System.IO.IOException,Message=Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host.,Source=System,StackTrace= at System.Net.ConnectStream.EndWrite(IAsyncResult asyncResult) at System.IO.Stream.<>c.<BeginEndWriteAsync>b__53_1(Stream stream, IAsyncResult asyncResult) at System.Threading.Tasks.TaskFactory1.FromAsyncTrimPromise1.Complete(TInstance thisRef, Func3 endMethod, IAsyncResult asyncResult, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.WindowsAzure.Storage.Core.Util.AsyncStreamCopier1.<StartCopyStreamAsyncHelper>d__8.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.WindowsAzure.Storage.Core.Util.AsyncStreamCopier1.<StartCopyStreamAsync>d__4.MoveNext(),''Type=System.Net.Sockets.SocketException,Message=An existing connection was forcibly closed by the remote host,Source=System,StackTrace= at System.Net.Sockets.Socket.EndSend(IAsyncResult asyncResult)
at System.Net.Sockets.NetworkStream.EndWrite(IAsyncResult asyncResult),'
Job ID: b5875b39-e3a2-d392-45b9-115c9701f661
Log ID: Warning

Before finally ending with:

TraceComponentId: TransferTask
TraceMessageId: TransferRunFailed
@logId: Error
FunctionName: Execute
jobId: b5875b39-e3a2-d392-45b9-115c9701f661
activityId: 5dad3178-ee10-4aaf-a8f0-381237d1d7ef
eventId: TransferRunFailed
message: Copy failed with error: 'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Blob write operation on path='<redacted by developer>' and blobName='<redacted by developer>' failed with error message: Unable to read data from the transport connection: The connection was closed..,Source=Microsoft.DataTransfer.ClientLibrary,StackTrace= at Microsoft.DataTransfer.Common.TasksCoordinator.CheckTaskFailureOrCancellation()
at Microsoft.DataTransfer.ClientLibrary.MultipartWriteSink.<WriteStreamInParallelAsync>d__93.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.DataTransfer.ClientLibrary.MultipartWriteSink.<WriteStreamInParallelAsync>d__93.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.DataTransfer.ClientLibrary.MultipartWriteSink.<WriteStreams>d__78.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.DataTransfer.ClientLibrary.MultipartWriteSink.Write(IEnumerable1 dataReader) at Microsoft.DataTransfer.Runtime.SinkStageProcessor1.ProcessInternal(IReadResult1 data) at Microsoft.DataTransfer.Runtime.BinarySinkStageProcessor.ProcessInternal(IReadResult1 data)
at Microsoft.DataTransfer.Runtime.PipelineProcessStageProcessor2.Process(TInput data) at Microsoft.DataTransfer.Runtime.PipelineProcessStageProcessor2.Process(TInput data)
at Microsoft.DataTransfer.Runtime.PipelineProcessStageProcessor2.Process(TInput data) at Microsoft.DataTransfer.TransferTask.CopyTask.DoCopy() at Microsoft.DataTransfer.TransferTask.CopyTaskBase.Execute(),''Type=Microsoft.WindowsAzure.Storage.StorageException,Message=Unable to read data from the transport connection: The connection was closed.,Source=Microsoft.WindowsAzure.Storage,StackTrace= at Microsoft.WindowsAzure.Storage.Core.Util.StorageAsyncResult1.End()
at Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob.EndPutBlock(IAsyncResult asyncResult)
at Microsoft.WindowsAzure.Storage.Core.Util.AsyncExtensions.<>c__DisplayClass7.<CreateCallbackVoid>b__5(IAsyncResult ar)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.DataTransfer.Runtime.AzureBlobMultipartRandomWritable.<WritePartAsync>d__18.MoveNext(),''Type=System.IO.IOException,Message=Unable to read data from the transport connection: The connection was closed.,Source=System,StackTrace= at System.Net.ConnectStream.EndWrite(IAsyncResult asyncResult)
at System.IO.Stream.<>c.<BeginEndWriteAsync>b__53_1(Stream stream, IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory1.FromAsyncTrimPromise1.Complete(TInstance thisRef, Func3 endMethod, IAsyncResult asyncResult, Boolean requiresSynchronization) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.WindowsAzure.Storage.Core.Util.AsyncStreamCopier1.<StartCopyStreamAsyncHelper>d__8.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.WindowsAzure.Storage.Core.Util.AsyncStreamCopier`1.<StartCopyStreamAsync>d__4.MoveNext(),'

I'm at a loss as to what the cause is. This only recently started to happen and is happening not only for my job, but a completely different job on the same server as well.

We've tried rebooting the server, reinstalling the IR, and using a different storage account but the issue still occurs.

Edit: I should add that the copy activity runs in a loop and not every iteration fails. Most succeed after a while. I've tried running the loop sequentially as well as limit the concurrency but that doesn't help any, either.

I would appreciate any guidance or help.

Thank you.

ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2022-06-09T16:08:43.597+00:00

Hi @Matthew Pruett ,

Thank you for posting query in Microsoft Q&A Platform.

Usually this kind of error comes when machine is running but the service with which request is trying to communicate is not available. Either it may stopped or crashed or is busy with another process. In our case if on-prem SQL server is available then may be its busy with some other process or with some other request.

Kindly make sure your on-prem SQL server resources are available for you to perform copy activity to avoid these kind of errors.

Please let us know how it goes. Thank you.
Matthew Pruett 96 Reputation points

2022-06-09T17:17:10.077+00:00

Thank you for the response @ShaikMaheer-MSFT

In this case, the SQL Server resources are within acceptable bounds. The sink is an azure blob container but other processes are able to write to the container just fine. This is quire bizarre since it only started happening despite the pipeline ran successfully before that. Switching containers doesn't seem to alleviate the issue.

I have noticed that it only happens with copy activities where the volume is larger than the ones that succeed. However the data size is still quite small. Only a few MB in some cases.
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2022-06-13T12:05:15.747+00:00

Hi @Matthew Pruett ,

I suspect that some process on SQL server machine may consuming CPU resources and resulting this issue. I may wrong too. Kindly check it once. If that is not the case may be understand the case, we may need to do deeper investigation with help of support ticket. Kindly feel free to raise Azure Support ticket if needed so that dedicated team can help you better.
https://learn.microsoft.com/en-us/azure/azure-portal/supportability/how-to-create-azure-support-request