Today post will be discussing more into Amazon RDS ( Relational Database Service ). How do We migrate our existing SQL Server database to Amazon RDS. Assuming You already have SQL Server Amazon Relational Database Services (RDS) database up and running. Next You will need following:
1. Your RDS Endpoint: “yyyy.xxxxxxxxxx.us-east-1.rds.amazonaws.com” for example
2. Your Master Username: “sa” for example
3. Your password
4. Install SQL Server Management Studio and connect to the SQL Server hosting your source dbase.
5. A dbase to be created on your RDS instance. To do this connect to the RDS instance
1: Create your tables
Create an empty shell of all your tables without any constraints on them. Start up SQL Server Management Studio (SSMS) and connect to your dbase (point 4 above). Then right click on your SQL database > Tasks > Generate Scripts. When you get to the part where you have to choose your objects, select only the tables.
When you get to the “Set scripting options” click on the Advanced button and change the following options:
Script USE DATABASE = False
Script check constraints = False
Script foreign keys = False
Script primary keys = False
Script Unique keys = False
Then generate your script and run it on your RDS dbase. Be careful not to run it on the master dbase. If you do you will receive an error.
2: Move your data
After created the empty tables in your RDS dbase you are ready to move the data across. You will be exporting the data to flat files and then importing them to your RDS database. Connect to your source database with SSMS and run the following script:
Select 'bcp ' + db_name() + '..' + name + ' out "c:\DataDirectory\' + name + '.dat" -E -n -S localhost –UUserName -PPassword'
from sysobjects where type = 'U'
order by name
Note that you will need to replace “UserName” and “Password” with the actual login and password to connect to your SQL Server. You should also replace “DataDirectory” with the directory where you want to store your exported data.
The output of the above script will be a list of bcp statements. Copy these into a text file and save it with a “.bat” extension. Then double click the file. The result should be that you will have files with the “.dat” extension in your directory. There should be one file for each table in your dbase.
Now run the following script from SSMS on your source dbase. This script will generate the bcp statements to pump the data into your RDS dbase. So now you will need to specify the following:
- RDSEndpoint = the Amazon RDS endpoint
- DataDirectory = location of your exported data files (same as above script)
- UserName = Login to your Amazon RDS endpoint
- Password = Password for your Amazon RDS endpoint
Select 'bcp ' + db_name() + '..' + name + ' in "c:\DataDirectory\' + name + '.dat" -E -n -SRDSEndpoint -UUserName -PPassword'
from sysobjects where type = 'U'
order by name
The output of the above script will be a list of bcp statements. Copy these into a text file and save it with a “.bat” extension. Then double click the file. The result will be that your RDS dbase is populated with data.
3: Create your other dbase objects
Generate a script for all your other dbase objects and constraints the same way as we did in step #1. The easier solution is using a tool that compares the schema of your source and RDS dbase and can generate a script to synchronize the differences. Search on Google for “compare and synchronize sql server database”.
Installed the tool, you can point it at your source and RDS dbase. Then you can generate a script to synchronize your dbases so that the RDS dbase is identical to the source one.
For full details please follow the link : instadb.com
Andreas Wasita 