Migrate a Microsoft SQL Server database to Aurora MySQL by using AWS DMS and AWS SCT - AWS Prescriptive Guidance

Migrate a Microsoft SQL Server database to Aurora MySQL by using AWS DMS and AWS SCT

Created by Mark Szalkiewicz (AWS) and Pavan Pusuluri (AWS)

R Type: Replatform

Source: Databases: Relational

Target: Amazon Aurora MySQL

Created by: AWS

Environment: PoC or pilot

Technologies: Databases; Migration

Workload: Microsoft

AWS services: Amazon Aurora

Summary

This pattern describes how to migrate a Microsoft SQL Server database that is either on premises or on an Amazon Elastic Compute Cloud (Amazon EC2) instance to Amazon Aurora MySQL. The pattern uses AWS Database Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT) for data migration and schema conversion.

Prerequisites and limitations

Prerequisites

  • An active AWS account

  • A Microsoft SQL Server source database in an on-premises data center or on an EC2 instance

  • Java Database Connectivity (JDBC) drivers for AWS SCT connectors, installed on either a local machine or an EC2 instance where AWS SCT is installed 

Limitations

  • Database size limit: 64 TB

Product versions

Architecture

Source technology stack

One of the following: 

  • An on-premises Microsoft SQL Server database

  • A Microsoft SQL Server database on an EC2 instance

Target technology stack

  • Aurora MySQL

Data migration architecture

  • From a Microsoft SQL Server database running in the AWS Cloud

AWS Cloud architecture showing VPC with private subnet containing SQL Server and Aurora MySQL databases.
  • From a Microsoft SQL Server database running in an on-premises data center

AWS Cloud architecture diagram showing on-premises to cloud migration using AWS SCT, DMS, and Aurora MySQL.

Tools

  • AWS DMS - AWS Data Migration Service (AWS DMS) helps you migrate your data to and from widely used commercial and open-source databases, including Oracle, SQL Server, MySQL, and PostgreSQL. You can use AWS DMS to migrate your data into the AWS Cloud, between on-premises instances (through an AWS Cloud setup), or between combinations of cloud and on-premises setups.

  • AWS SCT - AWS Schema Conversion Tool (AWS SCT) makes heterogeneous database migrations easy by automatically converting the source database schema and a majority of the custom code to a format compatible with the target database.

Epics

TaskDescriptionSkills required
Validate the source and target database version and engine.DBA
Create an outbound security group for the source and target databases.SysAdmin
Create and configure an EC2 instance for AWS SCT, if required.DBA
Download the latest version of AWS SCT and associated drivers.DBA
Add and validate the prerequisite users and grants in the source database.DBA
Create an AWS SCT project for the workload and connect to the source database.DBA
Generate an assessment report and evaluate feasibility.DBA
TaskDescriptionSkills required
Create a target Amazon RDS DB instance, using Amazon Aurora as the database engine.DBA
Extract the list of users, roles, and grants from the source.DBA
Map the existing database users to the new database users.App owner
Create users in the target database.DBA
Apply roles from the previous step to the target database.DBA
Review the database options, parameters, network files, and database links in the source database, and then evaluate their applicability to the target database.DBA
Apply any relevant settings to the target.DBA
TaskDescriptionSkills required
Configure AWS SCT connectivity to the target database.DBA
Convert the schema using AWS SCT.

AWS SCT automatically converts the source database schema and most of the custom code to a format that is compatible with the target database. Any code that the tool cannot convert automatically is clearly marked so that you can convert it yourself.

DBA
Review the generated SQL report and save any errors and warnings.DBA
Apply automated schema changes to the target or save them as a .sql file.DBA
Validate that AWS SCT created the objects on the target. DBA
Manually rewrite, reject, or redesign any items that failed to convert automatically.DBA
Apply the generated role and user grants and review any exceptions.DBA
TaskDescriptionSkills required
Determine the migration method.DBA
Create a replication instance from the AWS DMS console.

For detailed information on using AWS DMS, see the links in the "Related resources" section.

DBA
Create the source and target endpoints.DBA
Create a replication task.DBA
Start the replication task and monitor the logs.DBA
TaskDescriptionSkills required
Use AWS SCT to analyze and convert the SQL items within the application code.

When you convert your database schema from one engine to another, you also need to update the SQL code in your applications to interact with the new database engine instead of the old one. You can view, analyze, edit, and save the converted SQL code. For detailed information on using AWS SCT, see the links in the "Related resources" section.

App owner
Create the new application servers on AWS.App owner
Migrate the application code to the new servers.App owner
Configure the application server for the target database and drivers.App owner
Fix any code that's specific to the source database engine in the application.App owner
Optimize the application code for the target engine.App owner
TaskDescriptionSkills required
Apply any new users, grants, and code changes to the target.DBA
Lock the application for any changes.App owner
Validate that all changes were propagated to the target database.DBA
Point the new application server to the target database.App owner
Recheck everything.App owner
Go live.App owner
TaskDescriptionSkills required
Shut down the temporary AWS resources (AWS DMS replication instance and EC2 instance used for AWS SCT).DBA, App owner
Update feedback on the AWS DMS process for internal teams.DBA, App owner
Revise the AWS DMS process and improve the template if necessary.DBA, App owner
Review and validate the project documents.DBA, App owner
Gather metrics around time to migrate, percent of manual versus tool cost savings, and so on.DBA, App owner
Close the project and provide any feedback.DBA, App owner

References

Tutorials and videos