How to Migrate Microsoft SQL Server database on EC2 to Amazon Aurora

Introduction

Assume, an E-commerce application is running on an EC2 instance with the Microsoft SQL Server database. But having the Microsoft SQL Server database on EC2 is an unmanaged solution and with this database management becomes a tedious task for the database administrators and they need a managed solution for smooth operation.

Moreover, the managed solution should automatically provision the capacity as the database grows and high performance compared to traditional databases solutions. Instead of implementing a database on a managed solution, they also must migrate the database from Microsoft SQL Server to Amazon Aurora (MySQL). Database migration involves multiple complex steps, but two fundamental steps are the conversion of schema and code and the migration of data.

In this blog, we discuss Data Migration Service (DMS), Schema Conversion Tool, and steps to convert automated data migration from Microsoft SQL Server to Amazon Aurora (MySQL).

Data Migration Services

AWS Database Migration Service (AWS DMS) migrates your data quickly and securely from source databases to target databases from the frequently used open-source and commercial databases. During the migration, the source database remains operational and minimizes downtime for applications that rely on the database. AWS DMS supports both homogeneous and heterogeneous migrations. Homogeneous migration migrates data from the same databases as Oracle to Oracle, whereas in heterogeneous migration, data migrates between different databases like MySQL to Oracle. The wide range of source databases like Microsoft SQL Server, MongoDB, MariaDB, PostgreSQL, Oracle, etc., and target databases like Oracle, MongoDB, Amazon Aurora MySQL, and PostgreSQL compatible, etc. are supported by AWS DMS.

Figure 1: Homogeneous Database Migration

Figure 1 shows the homogeneous database migration that migrates data from the same source to target databases like Oracle on EC2, On-premises, AWS to Amazon RDS Oracle, Microsoft SQL Server on EC2, On-premises, AWS to Microsoft SQL Server, etc.

Figure 2: Heterogeneous Database Migration

Figure 2 shows the heterogeneous database migration that migrates data from source to target databases like Oracle on EC2, On-premises, AWS to Amazon RDS MySQL, Microsoft SQL Server on EC2, On-premises, AWS to Oracle, etc.

AWS DMS migrates databases from AWS to AWS, on-premises to AWS or vice versa, but either source or target database should be on AWS.

Schema Conversion Tool (SCT)

In heterogeneous database migration, the AWS Schema Conversion Tool (AWS SCT) plays a vital role in automatically converting source database schema and code objects, including views, stored procedures, and functions, to target database schema. If any objects are not automatically converted, they are explicitly marked so that they can be manually converted to complete the migration of data. Once schema conversion is complete, SCT can help migrate data from a range of sources to target databases.

The AWS Schema Conversion Tool supports the following conversions –

Source: https://aws.amazon.com/dms/schema-conversion-tool/?nc=sn&loc=2

Migrating from Microsoft SQL Server to Amazon Aurora (MySQL)

This post discusses the migration of Microsoft SQL Server running on an EC2 instance to an Amazon Aurora-MySQL compatible database on AWS. Figure 3 depicts, the Schema Conversion Tool (SCT) converting schema from the source database Microsoft SQL Server to Amazon Aurora MySQL and then automatically replicating data using AWS DMS.

Pre-requisites:

1. Create a key pair to connect to the EC2 instance.

2. Use the CloudFormation template to create an EC2 instance with Microsoft SQL Server, Amazon Aurora MySQL compatible DB instance, Virtual Private Cloud with subnets, and security group to access the EC2 instance.

3. Create a database on Microsoft SQL Server.

Steps to migrate Microsoft SQL Server to Amazon Aurora MySQL database:

Schema Conversion
This section demonstrates how the AWS Schema Conversion Tool is used for schema conversion from Microsoft SQL Server to Amazon Aurora (MySQL). Also, you can observe how SCT helps to explicitly mark the unconverted objects and recommend the changes required for conversion.

1.1 Connect to EC2 instance and install SCT

1.2 Create Database Migration Project using SCT

Select a sample database and analyze its assessment report as shown below. 

After analyzing the report, click next to make an entry in the target database form.

1.3 Convert the schema and modify the procedural code.

In SCT, select Assessment Report View and only check Generate Transfer Activity in the source database procedures to manually replace ‘print’ with the ‘select’ command and apply changes to the target database schema

Data Migration

This section demonstrates how to use the AWS Database Migration Service to migrate data from the source Microsoft SQL Server running on an Amazon EC2 instance to the target Amazon Aurora (MySQL and continually replicate database changes from the source to the target database.

2.1 Open SQL Server Management Studio and configure source and target databases.

2.2 Create DMS Replication agent, source and target endpoints.

 2.3 Create Data Migration Task and observe the migration in progress.

Conclusion

The blog discussed the procedure to migrate Microsoft SQL Server on EC2 to Amazon Aurora (MySQL) using AWS Schema Conversion Tool (SCT) and Amazon Database Migration Service (DMS). During this heterogeneous data migration, the source database remains operational and supports minimal downtime to the applications running on the database

About CloudThat

We, CloudThat incepted in 2012 as the first Indian organization to offer Cloud training and consultancy for mid-market and enterprise clients. Our business goal is providing global services on Cloud Engineering, Cloud Training and Cloud Expert Line. The expertise in all major cloud platforms including Microsoft Azure, Amazon Web Services (AWS), VMware and Google Cloud Platform (GCP) position us as pioneers in the realm.

With our Cloud Consulting we offer a wide-array of services that encompasses Cloud Consulting & Migration, Cloud Data Platform, DevOps & DevSecOps, Contract Engineering, Cloud Managed Services, and Cloud Media Services. We are on a mission to build a robust cloud computing ecosystem by disseminating knowledge on technological intricacies within the cloud space.

Our blogs, webinars, case studies, and white papers enable all the stakeholders in the cloud computing sphere.