Importing data on Amazon RDS for Microsoft SQL Server.

Recently Amazon Cloud started RDS for Microsoft SQL Server and currently support Microsoft SQL Server 2008 R2 and Microsoft SQL Server 2012. Since RDS currently doesn’t support restoring database from Backup it is really tedious job to import large database. AWS has detail documentation of export/import database with different method. As mention in documentation Bulk Copy is fastest method when you have big size database. Although everything mention in documentation I will walk you through my experience for importing database. 

Point to Remember:

1. Currently major version downgrade/upgrade not possible so be sure while creating instance on RDS.
2. If you are looking for provisioned IOPS then keep select option while generating instance since this will not configurable once instance created. To enable IOPS option later you may need to start from scratch.
3. Be sure about storage size since you may not able to change once instance created.
4. Once instance created you may able to create DB Snapshot which can be later use to generate new RDS instance but again this will not let you change Version of SQL Server, Storage size, IOPS etc.

Below are steps to exporting database using bulk copy method from local server and importing to RDS.

1. Connect to your local database which you want to export to RDS.
2. Select database, right click on it and go to Task >> Generate Script
3. Select database to export, Check the box "Script all objects in the selected database".
4. Make sure "Script Triggers" and "Script Foreign Keys" is set to False, Script for these two we will do it later once exporting of data completed.
5. Save generated script which we will use on RDS database.
6. BCP command will be used to generate export file and this will required to run individually for each table. Run below query in SQL Server management studio to generate BCP command all tables.
   [code:sql]select  ‘bcp [databasename].[dbo].[‘ + [name] + ‘] out C:\backup\[dbo].[‘ + [name] + ‘].dat -n -S localhost,1433 -U username -P password’  from sys.objects where type=’U’order by name[/code]
   Update database name, username and password according to your setup.
7. Open command prompt with Administrative access and copy/paste all commands generated using query in step 8 and run it. This will take time depending size of your database.
8. Until your data exported lets create RDS DB instance on AWS console and connect it through sql server management studio.
9. You may need to add CIDR in DB Security groups.  CIDR will be suggested on screen itself (your original CIDR may different if your machine is behind a proxy/firewall).
10. Once successfully connected with RDS in management studio create database and run script generated in step 4 to create all tables and other objects.
11. Ok, Now my database setup on RDS and hopefully local database exported in folder C:/backup/ and it is time to import data in RDS database. Same BCP utility we will use to import data, use below query to generate bcp command for all tables

    [code:sql]select ‘bcp [rds-databasename].[dbo].[‘ + [name] + ‘] in C:\backup\[dbo].[‘ + [name] + ‘].dat -n -S aws-servername,1433 -U username -P password -b 10000 -E’ from sys.objects where type=’U’order by name[/code]
    Update rds-databasename,aws-servername, username and password accordingly.
    Also do not forget to add switch -E, this will make sure auto increment key do not generated by sql instead it will use from imported data.

12. Copy generated script from management studio and run in command prompt, Do I need to mention that it will take good amount of time 🙂 

Reference Link

AMAZON Import/Export Guide

Step mention above is all about what I have experience during importing database. I will appreciate all kind of comment on it.