Transport error during executeBlock
MarkHeesom
Posts: 5
Hi,
We are comparing a SQL2014 database with an Azure database and looking to migrate the diffs automatically via vb.net from the SQL database to the Azure database.
We are encountering a transport level error when executing the migration sql :-
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
This is being run using the following code :
Dim sqlProvider As New SqlProvider()
sqlProvider.Options = compSession.Options ' Our compare session based on TableMappings between the SQL and Azure databases
' Dispose the execution block when not needed so all temporary files are cleaned up
Using execBlock As ExecutionBlock = sqlProvider.GetMigrationSQL(session:=compSession, runOnTwo:=True)
' Execute the SQL against the target database
Dim executor As New BlockExecutor()
executor.Status = New StatusEventHandler(
Sub(sender As Object, e As StatusEventArgs)
If e.Message IsNot Nothing Then setStatus(e.Message, False)
End Sub)
executor.ExecuteBlock(block:=execBlock, server:=toConnProps.ServerName, database:=toConnProps.DatabaseName,
integratedSecurity:=False, userName:=toConnProps.UserName, password:=toConnProps.Password)
End Using
When we see the error it seems to happen on or around the 20 minute mark but not on any particular table. I've had a search around for similar issues and this pointed to the server TCP Chimney Offload state, however this is disabled both on the source SQL 2014 server and server where the vb code is executing.
We use the same compare logic for a number of routines and it works fine between SQL and Azure, however in the instance that is failing to diff set is pretty large containing some pretty large table diffs.
Any help anybody could provide would be greatly appreciated!!!!
Many thanks,
We are comparing a SQL2014 database with an Azure database and looking to migrate the diffs automatically via vb.net from the SQL database to the Azure database.
We are encountering a transport level error when executing the migration sql :-
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
This is being run using the following code :
Dim sqlProvider As New SqlProvider()
sqlProvider.Options = compSession.Options ' Our compare session based on TableMappings between the SQL and Azure databases
' Dispose the execution block when not needed so all temporary files are cleaned up
Using execBlock As ExecutionBlock = sqlProvider.GetMigrationSQL(session:=compSession, runOnTwo:=True)
' Execute the SQL against the target database
Dim executor As New BlockExecutor()
executor.Status = New StatusEventHandler(
Sub(sender As Object, e As StatusEventArgs)
If e.Message IsNot Nothing Then setStatus(e.Message, False)
End Sub)
executor.ExecuteBlock(block:=execBlock, server:=toConnProps.ServerName, database:=toConnProps.DatabaseName,
integratedSecurity:=False, userName:=toConnProps.UserName, password:=toConnProps.Password)
End Using
When we see the error it seems to happen on or around the 20 minute mark but not on any particular table. I've had a search around for similar issues and this pointed to the server TCP Chimney Offload state, however this is disabled both on the source SQL 2014 server and server where the vb code is executing.
We use the same compare logic for a number of routines and it works fine between SQL and Azure, however in the instance that is failing to diff set is pretty large containing some pretty large table diffs.
Any help anybody could provide would be greatly appreciated!!!!
Many thanks,
Comments
I've done some digging around and I think what is most likely is that the connection is being closed by Azure. Azure may close your connection if you are exceeding the limitations of its resource usage. This would explain why it is working fine for a number of routines but not for this large one.
There are some forums here with people experiencing the same error:
https://social.msdn.microsoft.com/Forum ... getstarted
http://stackoverflow.com/questions/2323 ... ming-query
The posts are quite old but I hope this is of some help.
Kind Regards,
Nick
Redgate Support