Picture of Brian Love wearing black against a dark wall in Portland, OR.

Brian Love

Migrating Databases in Amazon RDS

Amazon’s Relational Database Service (RDS) enables developers to launch database instances in the cloud that are managed by Amazon.

What this means is that developers can focus more on development and less on minor upgrades to the database, replication, fault tolerance and backups. This all sounds great, but there is one minor issue: no file system access. While this is generally not an issue, it does become an issue if you are attempting to create/restore a SQL Server backup (.bak) file.

RDS <> .bak

Unfortunately, not having access to the file system on our RDS instances means that we cannot create or import a .bak file.

So, what are we left to do? Well, there is a pretty lengthy process outlined in the documentation as the solution for importing or exporting data for your SQL Server RDS instance:

Going through this process is time consuming and prone to errors. Thankfully, after going through this process several times, I found a better solution that is not documented in the AWS documentation.

SQL Azure Migration Wizard

The SQL Azure Migration Wizard makes the process of migrating databases (or any table/view/stored procedure) in, out, or between RDS instances a much easier and faster process. Although we are not using the SQL Azure service from Microsoft, this tool works very well with SQL Server running on an RDS instance.

Now, although the steps above are the same number (coincidentally) of steps, trust me, the SQL Azure Migration Wizard will save you a lot of time (likely, hours). To walk you through the process, I put together a video demonstrating the use of the SQL Azure Migration Wizard to copy data in and out of an RDS instance using SQL Server.

Connection Attempt Failed when using bcp

This may not apply to you, but I want to note this for those that might be running into the same issue.

When attempting to import/export a large table you may run into a TCP connection error I ran into this issue when running SQL Server Management Studio on an Windows EC2 instance and attempting to use either the bulk copy command (bcp) or the import/export wizard. The connection error that I received stated:

SQLState = 08S01, NativeError = 10060 Error = [Microsoft][sql server native client 11.0]TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. Error =[Microsoft][sql server native client 11.0]Communication link failure Error =[Microsoft][sql server native client 11.0]Protocol error in TDS stream


Screenshot showing TCP error in command prompt

The solution to this is to disable the TCP offloading on the Citrix PV Ethernet Adapter (NIC). To do this in your Windows EC2 instance you will need to:

This solved all of my TCP communication link failures when exporting large tables out of my RDS instance.