Persistent time out issues copying very large tables across SQL Server 2012 instances on Amazon RDS

smo sqlbulkcopy sql-server sql-server-2012 ssis

Question

To give some context, I'm currently running a SQL Server 2012 instance on Amazon RDS and I've had to move to a larger instance twice already. The first time SQLAzureMW was the way to go, but at the time no table was that significantly large. The second time, SQLAzureMW always timed out the source server on the bcp command with large tables (a few over 5 GB). Similarly, SSIS Import / Export Wizard also timed out. I found the source server was always the problem so I tried increasing the instance's class from an m1.medium to an m1.xlarge to no avail, the source server still always timed out before making any significant progress on the large tables.

In the end I ended up writing my own .NET program that simply ran a "SELECT * FROM [table] ORDER BY [id] OFFSET {0} ROWS" on the large source tables and pushed the results into SQLBulkCopy on the destination server. Again the source server timed out repeatedly but I wrapped the try and catch statements in a loop that would simply resume the query from the last point where SQLBulkCopy. That being said, I'm not exactly thrilled with this solution.

I'm considering building a solution around the Microsoft.SqlServer.Management.Smo.Transfer class but I'm afraid there might be the same problems with lack of recovery from a broken source connection.

I'd much rather an out of the box solution for this like SQLAzureMW was before tables got too large and that I'd expect SSIS Import Export Wizard to be. There has to be a better way.

Popular Answer

We were running into a similar situation: running SQLAZureMW on an Window server 2012 EC2 instance connecting to SQL Server 2012 RDS Instance. AWS support suggested the following changes on our EC2 instance and it seems to have solved all of our issues:

  1. Increase TCP/IP timeout value as described here (i'm not sure this is actually necessary) http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-firewall-guidance.html
  2. Disabling all TCP offloading for the network adapter.

Instructions from AWS:

Here are the steps to disable TCP Offloading: Go to the properties of the Citrix PV ethernet adapter Click Configure Go to Advanced Disable all of the following Properties: IPv4Checksum Offload Large Receive Offload (IPv4), Large Send Offload Version 2(IPv4), TCP Checksum Offload (IPv4), UDP Checksum Offload (IPv4)

Then as a final step run the following command from the command prompt:

netsh int ip set global taskoffload=disabled 
netsh int tcp set global chimney=disabled 
netsh int tcp set global rss=disabled 
netsh int tcp set global netdma=disabled



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why