Complex AWS Migrations (Technical Blog Series)

Introduction

Welcome to our final blog about complex migrations to AWS. This blog will look at complex AWS migrations, which in this case, we define as heterogeneous migrations involving databases where the source and target database engines are different.

This blog will cover the following:

Migrating a Database to another Database Engine using AWS SCT with an example migration from Microsoft SQL to AWS Aurora (MySQL Compatible).

Migrating a Database to another Database Engine using AWS SCT

Migrating a Database to another Database Engine tends to be difficult because:

1. Schema and database code objects within a database may not be fully understood or documented
2. Databases need refactoring to take advantage of cloud-native services such as the conversion of Microsoft SQL Server to Aurora MySQL

AWS Schema Conversion Tool (AWS SCT) - SCT can be used to convert your database schema from the source database engine (e.g., on-premises) to the target database engine (AWS) to allow you to migrate your database.

AWS SCT will assess and convert the source database schema and a majority of the database code objects, including views, stored procedures, and functions, to a format compatible with the target database. Any objects that cannot be automatically converted are clearly marked as action items with prescriptive instructions on how to convert so that they can be manually converted to complete the migration.

AWS SCT can also scan your application source codes for embedded SQL statements and convert them as part of a database-schema-conversion project. During this process, AWS SCT performs cloud-native code optimization by converting legacy Oracle and SQL Server functions to their equivalent AWS service, helping to modernize the applications at the same time as database migration.

Using AWS SCT simplifies the migration process, reduces the time and effort involved, and supports popular Database Engines, including Oracle, Microsoft SQL Server, PostgreSQL, and MySQL.

Example of Using the SCT Tool to migrate from Microsoft SQL to MySQL

This example demonstrates how to use the AWS Schema Conversion Tool for schema conversion from Microsoft SQL Server to Amazon Aurora (MySQL). AWS SCT helps you with the differences between the two dialects of SQL; and provides you with tips about how you can modify procedural code when needed to successfully migrate all database objects.

1. Connect to the EC2 Instance
You need to first connect to the EC2 instance, which will require a Security Group to allow RDP access. Go to the Remote Desktop Section of your EC2 instance within the AWS console and use your Key pair to get the Windows password. Download the Remote Desktop File to your client desktop and connect to the EC2 instance using your windows password. . Go to the Remote Desktop Section of your EC2 instance within the AWS console and use your Key pair to get the Windows password. Download the Remote Desktop File to your client desktop and connect to the EC2 instance using your windows password.

2. Install the Schema Conversion Tool on the EC2 Instance
Download the AWS Schema Conversion Tool onto the EC2 Instance. Unzip the downloaded file and then install the SCT, which is a wizard guided install accepting the defaults and Terms and Conditions. You should also download the appropriate drivers for your Source and Target databases from https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html

3. Create a Database Migration Project
Double-Click on the AWS SCT desktop icon and then click on File, New Project Wizard to create a Database Migration Project. This will start a wizard with the 5 steps shown below:

Choose a Source, select your source Database Engine, click down to select the appropriate one, in our case Microsoft SQL Server.

Step 2, Connect to the source database, entering your server name, SQL instance name, username and password. Test the connection and click Next.

3. Choose the Database Schema
You will be given a list of database schema. This example tests the Microsoft Northwind database which is selected here. Choose it, ticking the box and also highlighting the selected database, click Next.

4. Run the Database Migration Assessment
You will be shown a report which you can also save to PDF or CSV.

Scroll through the report to see more details including that all the storage objects and code objects can be converted to Amazon Aurora (MySQL compatible)

5. Choose a target and Test the Connection.

The Database Driver location is specified which was downloaded in step 2. Click on Finish. A Warning message is displayed “The database version that you connected to is 5.7.12, which is less than the recommended Amazon Aurora (MySQL compatible) (8.0.23)”. The latest MySQL version of Aurora RDS has been used.

You are shown the results of the Schema Conversion.

You can see that everything has converted successfully except the Ten Most Expensive Products stored Procedure which highlights the ROWCOUNT 10 as having an issue. It needs to be replaced in MySQL with LIMIT 10.

Conclusion

We hope you’ve found this series on Migrating to AWS to be a valuable guide and are excited about the many benefits of migrating to AWS. Whatever your requirements are, there is an AWS migration solution to fit, from single applications and operating systems to more complex migrations and hybrid/multi-cloud environments. However, if you require assistance, please reach out to our experienced cloud team; as an accredited AWS partner, we will help you with every stage of your migration journey cloud@transactts.com