How to Migrate Your SQL Server Database to Amazon RDS

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:

  1. Amazon Aurora
  2. Microsoft SQL Server
  3. Oracle
  4. PostgreSQL
  5. MYSQL
  6. 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:

  1. The first step would be to take a snapshot of the source RDS instance.
  2. Secondly, you will have to disable automatic backups on the origin RDS instance.
  3. Now, create your target database by disabling all foreign key constraints and triggers.
  4. Import all the logins into the destination database.
  5. The next step is creating the schema DDL with the help of the Generate and Publish Scripts Wizard in SSMS.
  6. Next, execute the SQL commands on your target database to create your schema.
  7. 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.
  8. Clean up the target database by re-enabling the foreign key constraints and triggers.
  9. 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 6Select 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.

Written by

Sudhi is part of Cloud Technology Partners & is a trusted advisor and strategic consultant to many C level executives and IT Directors. He brings 18+ years diverse experience covering software, IT operations, cloud technologies, and management. Have led several global teams in HP, Sun/Oracle, SeeBeyond and few startups to deliver scalable and highly available business/technology products and solutions. He has expertise in systems management, monitoring and integrated SaaS and on-premise applications addressing a wide range of business problems.

Related Posts

Albert Qian
— June 19, 2018

Preparing for the Microsoft Azure 70-535 Exam

The credibility of Microsoft Azure continues to grow in the first quarter of 2018 with an increasing number of enterprises migrating their workloads, resulting in a jump for Azure from 10% to 13% in market share. Most organizations will find that simply “lifting and shifting” applicatio...

Read more
  • Azure
  • Compute
  • Database
  • Security
— December 19, 2017

Database News: 7 Updates from AWS re:Invent 2017

Following AWS re:Invent 2017, we’ve counted more than 40 announcements of new or improved AWS services.  Today, we’ll be talking about our picks for the new database and storage services that should be on your radar for 2018.What’s New in the Database world?If you’re into Magic Q...

Read more
  • AWS
  • Database
  • reInvent17
— August 25, 2017

How to Set up a Web Application Hacking Lab

To learn something well requires practice, and ethical hacking is no exception. Unlike say, practicing the trumpet, practicing hacking has potential legal implications. This means that if you want to practice hacking, you need an environment. In this article, I’ll show you how to set up...

Read more
  • Database
  • Security
— July 24, 2015

AWS Database Options

There's an AWS database solution for just about any project you can imagine: the trick is properly understanding the job each of them does best.Got data? There's an AWS database with your name on it.Importing an existing MySQL, Oracle, Microsoft SQL, or PostgreSQL database into Amaz...

Read more
  • AWS
  • Database
— March 19, 2015

Two new Cloud Academy courses: AWS database and AWS networking fundamentals

AWS Database Fundamentals and  AWS Networking FundamentalsCloud Academy has just released two new courses from our Amazon Web Services Learning Tracks series. Now, in addition to the general introductory course, AWS Technical Fundamentals (AWS 110), you can also take AWS Networking Fu...

Read more
  • AWS
  • Database
  • Networking & CDN
— October 29, 2014

Part 2 of our course "Databases on AWS" now available

As I announced a few days ago, the second part of our great course "Database on AWS" is now available. The first part that we launched just a couple days ago and focusing on AWS RDS and DynamoDB got an overwhelming success, and I'm sure you'll like this new episode as well.In the secon...

Read more
  • AWS
  • Database
— October 27, 2014

Databases on AWS: a new course now available!

After the great success of our course Storage on AWS, I am really happy that we are just publishing a brand new course focused on Databases on AWS, an extremely interesting topic for many of you out there. This course is quite a long one and we split it into two parts: part 1 has just b...

Read more
  • AWS
  • Database
— October 8, 2014

New CloudAcademy lab: Create your first Amazon RDS database

Thanks to the commendable work of our Senior Devops Engineer Antonio Angelino, we are launching the third laboratory of our platform, and this time it is about AWS RDS, to get you started with your first RDS database.Labs are one of the most interesting features of our platform. They ...

Read more
  • AWS
  • Database