Amazon RDS is a web service that provides cloud database functionality for developers looking for a cost-effective and simple way to manage databases. If you’re looking to migrate your existing SQL database to RDS, this is the guide for you.
RDS offers six database engines:
- Amazon Aurora
- Microsoft SQL Server
- Oracle
- PostgreSQL
- MYSQL
- MariaDB
With RDS, there is no need for you to buy any other rack and stack hardware or install any software.
The complexity of moving your existing Microsoft SQL Server to Amazon RDS is mostly determined by the size of your database and the types of database objects which you are transferring.
For example, migrating a database which has data sets on the order of gigabytes along with stored triggers and procedures is going to be more complex than migrating a modest database with only few megabytes of test data and no stored procedures or triggers.
Why You Might Consider Migrating Your SQL Database to Amazon RDS
RDS allows developers to set up database instances in the cloud. Developers are relieved of the complexity of managing and maintenance of the database. Instead, they can focus on developing successful products.
There’s one issue, however: There is no file system access. Though this is usually not a huge problem, it becomes a concern if you are trying to restore or create an SQL Server backup (.bak) file.
The biggest setback unfortunately with an Amazon SQL Server RDS instance is that it cannot restore the data from .bak files or import .bak file. When a situation like this arises, what should a developer do?
Editor’s Note: AWS launched a native SQL server backup and restore feature in late July. You can read more about it here: Amazon RDS for SQL Server – Support for Native Backup/Restore to Amazon S3.
For these situations, a developer can migrate his SQL Server database to Amazon RDS. This may sound like a daunting task, but it is in fact quite easy.
In this post, we have tried to provide you with some easy steps to migrate your SQL Server database to Amazon RDS:
- The first step would be to take a snapshot of the source RDS instance.
- Secondly, you will have to disable automatic backups on the origin RDS instance.
- Now, create your target database by disabling all foreign key constraints and triggers.
- Import all the logins into the destination database.
- The next step is creating the schema DDL with the help of the Generate and Publish Scripts Wizard in SSMS.
- Next, execute the SQL commands on your target database to create your schema.
- You can use either the bulk copy command (cp) or the Import/Export Wizard in SSMS to migrate your data from the origin database to your target database.
- Clean up the target database by re-enabling the foreign key constraints and triggers.
- Again re-enable the automatic backups on the source RDS instance.
Thankfully, after experimenting with this process many times, we found a better solution not documented in the AWS documentation.
SQL Azure Migration Wizard
To save time and avoid errors, we have discovered a new and better solution called the SQL Azure Migration Wizard. With SQL Azure Migration Wizard, the process of migrating databases (or anything including views/tablse/stored procedures) in, out, or between RDS instances is much easier and faster.
To migrate your SQL database to Amazon RDS using SQL Azure Migration Wizard, follow these easy steps.
Step 1: Download the SQLAzureMW Tool
Download SQL Azure Migration Wizard on CodePlex. Next, you need to extract the SQLAzureMW.exe file. You can utilize SQL Server Management Studio for connecting your local SQL server and Amazon Web Service RDS instance. But, before doing all this, make sure that you have a good connection to these two servers.
Step 2: Begin the Migration
Double click on the SQLAzureMW.exe file. A page will appear on your screen and what you now need to do is to select Database as an option under the Analyze/Migrate category. Once you do this, click on the Next button.
Step 3: Source Database Tasks
Now enter your Source SQL Server Database connection details and click on the Connect button.
Choose the source database and click on the button that says ‘Next.’ Then select an option named as ‘Script all database objects’.
This option can enable to do the complete migration of the database. But if you don’t want to migrate entire database then you select an option that says ‘Select specific database objects.’
Step 4: Create Scripts
Create scripts for all selected SQL server objects. You should save the script on local hard drive and the move ahead by hitting a click on a button ‘Next’.
Step 5: Destination Database Process
Now you have created a script of your database. You will now be required to enter your RDS SQL Server connection credentials and then connect it.
Step 6: Select the Target Database
Choose the target database that you would like to migrate. If you have not created any database earlier, then create a new one using Create Database option and go next. Be sure to do a quick check to confirm if there are any errors.
Step 7: The Grand Finale
You can now verify your SQL Server Management Studio and check all the migrated data.
As you can see, SQL Azure Migration Wizard saves a lot of time.
You will have to modify settings in your corporate firewall if your database is on-premises. In case your database is already hosted on Amazon Web Services, you can also add an entry to your instance’s security group.
Next, what you have to do is simple: launch and prepare an Amazon RDS instance running SQL. Then restore the database from the SQL dump and take note of the current log file name. Now you will use the database dump to start the RDS instance.
Once the RDS instance is initialized, you will be required to run the replication stored procedures that are supplied as part of the release to configure the RDS instance. Once your RDS instance matches any changes that have taken place, the application configuration can be modified to use it in preference to the existing version.
Summary
Thus sums up the process on how to migrate a SQL Server database to Amazon RDS. The process of data migration is not a very complicated process but a very easy one indeed. We hope that this post was useful enough in helping all those who want to migrate their SQL Server Database to Amazon RDS.
