Tools to Move Database O-Premise to Amazon RDS or SQL Azure


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.

Sample commands:

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

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings


Exporting Data from SQL Server to Microsoft ACCESS(MSACCESS) Database

Recently I worked on a requirement where we had to export data from few tables in SQL Server 2008 R2 to Microsoft Access database. Initially it seemed to be a simple request, but the challenge we faced was, there was no MSAccess database available or MSAccess software installed on any of the servers.

So, first we had to work on creating a MSAccess database and then export the data from SQL Server to MSAccess database.

How to Create MSAccess database when Microsoft Access is not installed on any of the servers?

Follow the below steps to create MSAccess database

1. Click Start -> All Programs – Administrative Tools -> Click on Data Sources (ODBC) tool which will open a window.

2. Click Add button -> Then you get an option to choose a driver there we need to select Microsoft Access related driver.

MSAccess Driver Selection

Selecting MSAccess driver

3. Click Finish

4. Now, we will be on Microsoft Access Setup window

5. Click on Create button

6. Choose the path where you want to save the MSAccess database physical file and provide a name to the database.

MSAccess Path and Database Name

MSAccess Path and Database Name

7. Click OK, which will give us a message that the new MsAccess database with the specified name has been created.

8. Browse to the path and we will be able to see the physical MSAccess database file.

Now, next step is to Export the data from SQL Server Database to newly created MSAccess database. Follow below steps to export the data

1. Connect to the SQL Server instance from SQL Server Management Studio.

2. Right click on the databases where the tables exist which you want to export -> Select Tasks -> Export Data, which will open a Export Wizard

3. Choose Source Server details and click next
Data Source – Leave default
Server Name – Choose the SQL Server instance name where tables exist
Select the authentication type
Make sure database name is correctly listed

4. Choose Destination Server details
Destination – Select Microsoft Access related driver
FilaName – Browse to the path and selected the MSAccess database which we created

5. Click On Advanced button and click Test Connection to make sure connection can be made to the Access database file

6. Click OK and Click Next

7. Select the radio button “Copy data from one or more tables or views” and click next

8. Select the tables which you want to export and once all required tables are selected, click next

9. Select the check box “execute immediately” and click next

10. Click Finish which will export the data from SQL Server database tables to the MSAccess database file.

Now, we can move the MSAcccess database file to other server to access the data

Happy Reading!

Keerthi Deep