With growing popularity of cloud technologies like Amazon RDS and SQLAzure, there has been lot of interest about understanding these technologies. These cloud solutions are cost effective but are limited on many aspects, one of which is moving data or databases between on-premise SQL Server instance to Amazon RDS and SQLAzure.
Mostly DBAs use backup/restore method to move or migrate databases/data from one server to another server or between SQL instances, but when it comes to Amazon RDS and SQLAzure, Restore is not supported as they do not provide file system level access, so this method is ruled out. Then what other methods are available for us to move data from on-premise SQL Server database to database on Amazon RDS and SQLAzure.
Below are some of the options which are available to move data from on-premise SQL Server database to database on Amazon RDS and SQLAzure.
Generate Scripts – Script create database, and its objects like table schemas, views, Stored Procedures, triggers, users, database roles and permissions, etc. Note that SQL Server Logins need to be created before hand to be able to map the database users to logins. This method can also be used to transfer data, but that will create huge file and can be difficult in writing to and reading from the file and makes things complicated, so better to use this method to create empty database with schemas only.
Import/Export Wizard – SQL Server Management Studion has buit-in tool which is Import/Export wizard which can be used to transfer schema and data. This creates a SSIS package to transfer the data and mostly is easy to create and implement. However there can be some issues with Identity columns, etc, so need to use it carefully and choose proper options.
BCP – For users who are comfortable with command line can opt for BCP which can be used to export data to dat file and then import that data into Amazon RDS or SQLAzure.
For moving data from on-premise to dat file
BCP.exe “[HR].[dbo].[Emp]” out “C:\AmazonRDS\BCPData\dbo.Emp.dat” -E -n -C RAW -S ServrName\SQLInstName -T
For moving data from dat file to Amazon RDS
BCP.exe “[HR].[dbo].[Emp]” in “C:\AmazonRDS\BCPData\dbo.Emp.dat” -E -n -C RAW -S AmazonRDSConn -U AmazonRDSAdminUser
SQL Database Migration Wizard – Users who are comfortable using GUI tool to take care of data transfer can use SQL Database Migration Wizard which is available in CODEPLEX, which is simple to use. This can be used for both AmazonRDS and SQLAzure.
If you are confused on which tool or which of these methods to choose from, then you may ask yourself questions like do we need to move the data once and will never going to move again and if you prefer GUI method in which case you can using GUI option using SQL Database Migration Wizard or Import/Export in SSMS. If you may require to transfer data periodically, then you may choose scripts/BCP command and use SQL Agent jobs to run it when ever required.
Hopefully this answers all your questions on how to move data between on-premise SQL Server database and AmazonRDS or SQLAzure.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings