Migrating to AWS: Common AWS Database Migration Tools

Ubertas Consulting has helped many companies migrate from Cloud and on-premises to AWS over the years. Typically, the most complicated aspect of any migration is its databases.

This complexity usually occurs for three reasons:

1. There are usually a lot of functions and stored procedures running on the databases, which are crucial to the correct operation of the DB and application; these can sometimes be difficult to capture or migrate
2. Sometimes the databases need to be refactored as part of the migration – for example, when the database is migrated from on-premise MS SQL to Aurora MySQL in AWS
3. Loss-less data transfer and synchronisation is too difficult – a particular challenge with always-on workloads; exacerbated further when DB I/O is volatile

To combat these issues, we leverage two main AWS migration services:

• AWS Schema Conversion Tool (SCT)
• AWS Database Migration Service (DMS)

AWS Schema Conversion Tool

The AWS Schema Conversion Tool (SCT) can be used to convert your database schema from the source database (e.g. on-premise) to the target database (AWS). SCT supports OLTP conversions, data warehouse conversions and NoSQL database conversions.

SCT has two overall use cases:

1. Homogenous Migrations
   This occurs when the source database platform is the same as the target database platform. For example, migrating from an on-premise MySQL database to a MySQL database in AWS.
   SCT automatically converts the source database schema and the majority of database code objects – including views, stored procedures, and functions – to a format compatible with the target database. It is also able to report objects that cannot be converted automatically, allowing manual intervention to take place. This ensures that the target database’s schema is as close as possible to the source, minimising post-migration errors.
2. Heterogeneous Migration
   This occurs when the source database platform is different to the target database platform. For example, migrating from an on-premise MS SQL database to a PostgreSQL database in AWS.
   As the name implies, the Schema Conversion Tool can perform dynamic conversions of schemas between database engines. This is particularly useful when migrating and updating legacy systems to AWS, for example.

AWS Database Migration Service

AWS Database Migration Service (DMS) helps migrate databases to AWS quickly and securely, with minimal downtime. It supports both homogenous migrations – such as Oracle to Oracle, MySQL to MySQL, etc – and heterogeneous migrations between different database engines – such as Microsoft SQL Server to Amazon Aurora, etc.

Without using tools like DMS, migrating a relational database like MS SQL or MySQL would require a set of steps similar to:

1. Identify or create a read replica
2. Perform staticSQL dump (from the read replica)
3. Import staticSQL dump on the target database

This is simple enough. But, in a situation facing highly volatile data (high write activity), the target data is out of date even before the SQL dump was restored on the target database. Repeating the static dump method will not resolve this, no matter how many times you perform it. For this reason, DBAs will typically perform data migrations within safe maintenance windows (typically in the small hours).

DMS works differently, offering a dynamic data migration. That is, it will persist two active DB connections – one to the source, the other to the target – and perform an ongoing real-time synchronisation of data from source to target. Deltas are performed entirely automatically, meaning you can sync to the target from a hot DB’s read replica, A/B test both systems in parallel if you so wish, and then switch over to the target DB. After a nominal period – to allow read replication to propagate the target – you simply disable DMS and end the data migration process. In most cases, there is zero downtime during the switchover, although this will depend on how your application’s traffic is routed.  

How Does DMS Work?

The diagram above illustrates how the DMS replication works.

The first step is to create a DMS replication instance in AWS.  For additional performance, it is recommended that you spin up the DMS replication instance in the same subnet as your target database(s).

The next step is to create source and target endpoints for the two databases respectively.  You will need to configure your VPC Security Groups and NACLs, to allow the DMS instance to connect to both the source database server and the target database in AWS.

The next step is to start a migration task, which consists of three main phases:

• Full Load
  Essentially a static dump and import; all the data from the source database is loaded to the target database.  While the full load is in progress, any changes that occur on the source database are cached on the replication server, ahead of the next phase.
• Cached Changes Application
  Once the full load is complete, the replication server will apply the cached changes to the target database.
• Ongoing Replication (Change Data Capture)
  After the cached changes are applied, DMS moves to the ongoing replication phase, applying changes as transactions (deltas).

This is a very brief introduction to some of the tooling and services available to add your migrations. It would be impossible to cover all of them in considerable depth in a single blog article, not to mention that your own migration requirements may present unique challenges.

To find out more about the services discussed in this blog, please refer to the links below:

1. AWS Schema Conversion Tool (SCT)
2. AWS Database Migration Service (DMS)

Fayomi Fashanu
Solutions Architect, Ubertas Consulting

LinkedIn