This tutorial assumes that you already have your SQL Server Amazon Relational Database Services (RDS) database up and running. You will need the following:
1. Your RDS Endpoint: “trai.cjss4rqkzige.us-east-1.rds.amazonaws.com” in the example below
2. Your Master Username: “sa” in the example below
3. Your password
4. Install SQL Server Management Studio and connect to the SQL Server hosting your source database.
5. A database to be created on your RDS instance. To do this connect to the RDS instance
Step #1: Create your tables:
What you need to do first is create an empty shell of all your tables without any constraints on them. So start up SQL Server Management Studio (SSMS) and connect to your database (see point 4 above). Then right click on your SQL database > Tasks > Generate Scripts… (note that this is not the same as right clicking on your database and choosing “Script database as”)
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” you need to 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 database. Be careful not to run it on the master database. If you do you will receive an error.
Step #2: Move your data
Now that you have created the empty tables in your RDS database 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:\Temp\' + 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 database.
Now run the following script from SSMS on your source database. This script will generate the bcp statements to pump the data into your RDS database. 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:\Temp\' + name + '.dat" -E -n -S RDSEndpoint -Uusername -Ppassword -q -k' 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 database is populated with data.
Step #3: Create your other database objects
At this point I like to cheat a little bit. What you could do is generate a script for all your other database objects and constraints the same way as we did in step #1. But the easier solution is to use a tool that compares the schema of your source and RDS database and can generate a script to synchronize the differences. There are several such tools that offer a free trial version on the internet. Just do a search on Google for “compare and synchronize sql server database”. I personally like to use the RedGate SQL Compare tool.
Once you have installed the tool of your choice, you can point it at your source and RDS database. Then you can generate a script to synchronize your databases so that the RDS database is identical to the source one.
That is all you need to do to migrate your database to Amazon Relational Database Services. The only remaining part that has not been described is your database security. The easiest way to do this is to just create a new account to connect to your database and to update your application to use this account. You could also script out the security of your SQL Server and create the logins on the RDS instance. The procedure for this is described here.
Next steps:
- Make sure your migrated database is identical to the source: validate your SQL Server RDS Migration
- Take a SQL Server baseline so that you can compare performance of your databases before and after the migration.
If you need help don’t hesitate to get in touch.