thecfguy

A Unique Developer

Importing data in Amazon RDS DB Instance 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.
    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

    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

    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

    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.

UPDATE:
I forgot step for creating foreign key/trigger skipped in step 4. 

This will required some manual effort. You can use regenerate script feature with enabling foreign key and trigger script enable but you have to keep selected Table Create script to true otherwise script will not generated.

Foreign key script will be at bottom so it will be easy to copy and paste to different file but for trigger you need to manually search and find to separate out.

Another way to do this is use third party software for schema comparision. If you are using SQL Server 2012 or higher below link will be useful for schema comparision as well as data comparision which you may want to do on next step.
http://blogs.msdn.com/b/ssdt/archive/2013/06/24/announcing-sql-server-data-tools-june-2013.aspx