Migrate a Microsoft SQL Server database to Aurora MySQL by using AWS DMS and AWS SCT
Created by Mark Szalkiewicz (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
Microsoft SQL Server 2008, 2008R2, 2012, 2014, 2016, and 2017 for the Enterprise, Standard, Workgroup, and Developer editions. The Web and Express editions aren't supported by AWS DMS. For the latest list of supported versions, see Using a Microsoft SQL Server Database as a Source for AWS DMS. We recommend that you use the latest version of AWS DMS for the most comprehensive version and feature support. For information about Microsoft SQL Server versions supported by AWS SCT, see the AWS SCT documentation.
MySQL versions 5.5, 5.6, and 5.7. For the latest list of supported versions, see Using a MySQL-Compatible Database as a Target for AWS DMS.
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
From a Microsoft SQL Server database running in an on-premises data center
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
| Task | Description | Skills 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 |
| Task | Description | Skills 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 |
| Task | Description | Skills 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 |
| Task | Description | Skills 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 |
| Task | Description | Skills 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 |
| Task | Description | Skills 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 |
| Task | Description | Skills 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 |
Related resources
References
Tutorials and videos
