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.

Avatar

Written by

Sudhi Seshachala

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

Avatar
Stuart Scott
— July 18, 2019

AWS Fundamentals: Understanding Compute, Storage, Database, Networking & Security

If you are just starting out on your journey toward mastering AWS cloud computing, then your first stop should be to understand the AWS fundamentals. This will enable you to get a solid foundation to then expand your knowledge across the entire AWS service catalog.   It can be both d...

Read more
  • AWS
  • Compute
  • Database
  • fundamentals
  • networking
  • Security
  • Storage
Albert Qian
Albert Qian
— June 19, 2018

Preparing for the Microsoft Azure 70-535 Exam

(Update) The Azure 70-535 exam was retired on December 31, 2018, and it was replaced by the AZ-300 and AZ-301 exams. To prepare for these exams, we recommend the Cloud Academy's AZ-300 Exam Preparation: Technologies for Microsoft Azure Architects and the AZ-301 Exam Preparation: Designi...

Read more
  • Azure
  • Compute
  • Database
  • Security
Avatar
Alex Casalboni
— 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 Quad...

Read more
  • AWS
  • Database
  • re:Invent 2017
Avatar
Ben Lambert
— 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
Avatar
Michael Sheehy
— 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 Am...

Read more
  • AWS
  • Database
Avatar
Marcin Kurc
— March 31, 2015

How to Prepare for the Cloud Services Revolution

Infrastructure as a Service (IaaS) Infrastructure as a Service (IaaS) provides a completely encapsulated infrastructure for building and running applications. While everyone knows the basics, we’re still at the very beginning of the revolutionary business models that are now possible w...

Read more
  • Database
Avatar
David Clinton
— March 19, 2015

Two New Cloud Academy Courses: AWS Database and AWS Networking Fundamentals

(Update 2019) We’ve released some great new content over the past few years, visit Cloud Academy’s Training Library for the latest training material. AWS Database Fundamentals and AWS Networking Fundamentals Cloud Academy has just released two new courses from our Amazon Web S...

Read more
  • AWS
  • Database
  • Networking & CDN
Avatar
Christian Petters
— December 15, 2014

Architecting on AWS: Optimizing the Application Design

In our practice, we hear a variety of misconceptions and misinterpretations in relation to the benefits of moving workloads 'into the cloud'. You should be very wary if someone wants to make you believe that the pure migration of a traditional application to a cloud services vendor will...

Read more
  • AWS
  • Database
  • Storage
Avatar
Andrea Colangelo
— October 29, 2014

Part 2 of Databases on AWS Course Now Available

As I announced a few days ago, the second part of the Cloud Academy's Database on AWS course is now available. The first part Introduction to Databases that we launched just a couple of days ago and focusing on AWS RDS and DynamoDB got an overwhelming success, and I'm sure you'll like t...

Read more
  • AWS
  • Database
Avatar
Andrea Colangelo
— 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 decided to split it into two parts: Introd...

Read more
  • AWS
  • Database
Avatar
Praveen Kumar Muppala
— October 24, 2014

How to Deal With AWS RDS Maintenance Windows

Amazon RDS is one of the best MySQL-based DBaaS services from Amazon AWS. It provides high availability, resizable capacity, and consistent performance to your applications. To take advantage of the RDS features, we need to design, operate and apply the best practices to RDS to utilize ...

Read more
  • AWS
  • Database
  • RDS
Avatar
Andrea Colangelo
— October 8, 2014

New Cloud Academy Lab: Create Your First Amazon RDS Database

Thanks to the commendable work of our Senior DevOps Engineer Antonio Angelino, we are launching the third Hands-on on the Cloud Academy platform: Create Your First Amazon RDS Database Hands-on Lab, to get you started with your first RDS database. Hands-on Labs are one of the most int...

Read more
  • AWS
  • Database
  • RDS