How to Migrate Databases to RDS

RDS allows developers to set up database instances in the cloud. Developers are relieved of the complexity of managing and maintaining 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 a SQL Server backup (.bak) file.

This blog explains ways to migrate your SQL Databases to Amazon RDS.

The initial release of Amazon AWS RDS lacked the ability to create or to restore from native backup. It was viewed as a considerable restriction because it meant that an individual database could only be moved in or out of RDS by scripting it up using tools such as the SQL Server Import and Export Wizard.

In July 2016, however, AWS announced implementation support for native backups. If you have your own on-premises database that you want to move to RDS you can simply back up, upload, and restore it onto an RDS SQL Server. Similarly, you can copy a RDS database to your own local SQL Server instance, and copy a single database from one RDS server to another.

DB Migration Wizard

The SQL Database Migration Wizard is a free, open-source tool designed to migrate your on-premises SQL Server databases. It offers such features as the ability to analyze your source database, T-SQL scripts, and tracing files for the SQL database compatibility — easily achieved with the following steps:

1.      Download the SQLAzureMW Tool, then connect your local SQL Server and Amazon Web Service RDS instance. Ensure you have a good connection between these two servers.
2.      Begin the DB Migration by double-clicking on SQLAzureMW.exe file. Then, on the page that appears, select Database as an option under the Analyze/Migrate category. Then click Next.
3.      Enter your Source Server Database connection details and click Connect. Choose the source database; click Next.
4.      Select the Script All Database Objects option to perform a complete migration of the database. You can Select Specific Database Objects to migrate only parts of the database.
5.      Generate scripts on all selected SQL server objects and save them on your local hard drive; click Next.
6.      Key in your RDS SQL Server connection information and then connect to it.
7.      Select the target database that you would like to migrate. Perform a quick check to detect any errors.
8.      Finally, verify your SQL Server Management Studio and check all the migrated data.

BCP          

The BCP utility provides an efficient way of transferring data from the source database to your DB instance. With BCP utility, one can generate the export file and will be required to run them individually for each table.

Run the query below in SQL Server management studio to generate BCP command on all tables:

select 'bcp [databasename].[dbo].[' + [name] + '] out C:\backup\[dbo].[' + [name] + '].dat -n -S localhost,1433 -U username -P password' from sys.objects where type='U'order by name

Backup and Restore

How do you create a backup of an on-premises database and restore it to the RDS database? The key steps are:

1.  Create a S3 storage bucket in the same region, if you don’t have one already
2.  Assign the RDS SQL Server Instance to an option group containing the SQLSERVER_BACKUP_RESTORE option
3.  Specify an IAM role to allow RDS to access S3 and point to a bucket
4.  Upload the On-Premise Database Backup to S3 bucket created in Step 1
5. Run a special stored procedure to restore the database to the RDS SQL Server instance using SQL Management Studio

rds_backup_database – Back up a single database to a S3 bucket.

rds_restore_database – Restore a single database from S3.

rds_task_status – Track running backup and restore tasks.

rds_cancel_task – Cancel a running backup or restore task.

There you have it. The new support for native backups by AWS has made it easy to migrate database to Amazon RDS. You can now easily back up, upload and restore your on-premises database onto an RDS SQL Server in a few steps.

If you would like more information on how to migrate your database to Amazon RDS, please contact Datavail today. Datavail is a specialized IT services company focused on Data Management with solutions in BI/DW, analytics, database administration, custom application development, and enterprise applications. We provide both professional and managed services delivered via our global delivery model, focused on Microsoft, Oracle and other leading technologies.

For additional resources, please download our white papers.

Datavail Script: Terms & Conditions

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.