Another Database Migration Playbook goes live—migrate from Microsoft SQL Server to Amazon Aurora MySQL!

We’re excited to present the first edition of the Microsoft SQL Server to Amazon Aurora MySQL Compatibility Migration Playbook. AWS Database Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT) help you reduce the effort associated with migration from commercial engines to open-source and Amazon-managed databases. Thus, they help reduce cost and avoid vendor lock-in. This playbook is designed to assist database administrators tasked with migrating existing SQL Server databases to Amazon Aurora with MySQL compatibility.

The playbook focuses on the automatic conversion capabilities of AWS SCT and discusses alternatives for any limitations in the automatic conversion process. It highlights the differences, incompatibilities, and similarities between SQL Server and Aurora MySQL and covers a wide range of topics. These include T-SQL, configuration, high availability and disaster recovery (HADR), indexing, management, performance tuning, security, and physical storage. The playbook includes a brief overview of the tool’s features, discusses migration challenges and workarounds, and provides detailed examples.

This is the second playbook we’ve published. You can find the first playbook, Oracle to Amazon Aurora with PostgreSQL Compatibility Migration Playbook, on the AWS whitepaper website.

What can I do with this playbook?

The Database Migration Playbooks are a series of guides focused on best practices for creating successful blueprints for heterogeneous database migration. The playbooks complement existing automated and semiautomated database migration solutions and tools from Amazon, including SCT and DMS.

You can use the Database Migration Playbook as a reference to investigate the individual action codes generated by the AWS SCT tool. Or you can use it to explore a variety of topics where you expect to have some incompatibility issues.

All heterogeneous migration projects end up using both automated tools and manual conversions, which required DBA and developer know-how. The playbooks capture a significant amount of the DBA know-how, discuss migration challenges, and offer best practices for common migration tasks, including code examples.

How to use the Database Migration Playbook

We recommend that you read the playbook’s short introduction, which contains an overview of how to use it as your migration guide. We also provide links to sections that you might want to begin with, depending on how you plan to use the playbook.

Tables of feature compatibility

The Tables of Feature Compatibility index (page 11) provides a high-level reference for all the topics discussed in depth throughout the playbook. It also provides a summary of key differences and two visual indicators.

The green and red database icons indicate the feature compatibility between SQL Server and Aurora MySQL.

The gear icons indicate the automation level for migration, offered by AWS SCT.

For example, in the ANSI SQL section (page 13 in the playbook, shown following), you can immediately see that both constraints and table creation code are relatively compatible between the two databases. Constraints and table creation code can also be migrated automatically by SCT. You can also see that migration of CTEs requires manual conversion. Following the links takes you to the relevant topics where you can find an in-depth discussion and workaround suggestions.

This index is most useful as a general reference, regardless of whether you plan to use or have already used AWS SCT and AWS DMS.

The following image shows ANSI SQL feature and automation level, from page 13.

AWS schema and data migration tools

This section, which begins on page 23, provides an overview and a visual walkthrough of the process for using AWS SCT and AWS DMS.

On page 38, you find the SCT Action Code Index. Use this index to help you deal with the action codes reported by AWS SCT for your databases. The SCT Action Code Index focuses on the capabilities and limitations of the SCT codes. It provides a link to the SQL Server topic that these action codes are associated with. The related Aurora MySQL topic always follows immediately.

This index is most useful after running the AWS SCT, for methodologically dealing with the generated action codes.

The following image shows AWS SCT action codes for converting constraints, from page 39.

Migration topics

Following the AWS SCT and DMS sections are the individual topics that discuss the migration details. Each topic consists of two sections.

The first section, titled “Migrate from SQL Server: …” is a short reminder of how the feature is used in SQL Server. This is followed by the “Migrate to Aurora MySQL: …” section, which discusses how the feature works in Aurora MySQL, migration considerations, and suggests potential workarounds. For example, the following page discusses the differences regarding unique constraints.

The following image shows migrating constraints from page 58.

Syntax reference and code examples are also provided, where relevant. For example, if your databases are using either TIMESTAMP or ROWVERSION columns, you can work with the following migration example.

About the authors

Ami Levin is senior instructor, data tier architect, data modeler, database designer and SQL developer with over 20 years of experience. He has designed, implemented, reviewed, and optimized some of the most challenging, mission critical database environments. Ami is a seasoned speaker, instructor and mentor, as well as an author of white papers, technical guides and articles.

Eran Schitzer is a product manager for Database Migration Services at Amazon Web Services.