Moving data with the AWS Database Migration Service

What is it? Why do I need it?
Getting set up
Create a task using the UI
Use the rules
Transform
Create task
Additional resources

What is it? Why do I need it?

AWS Database Migration Service (AWS DMS) is a web service that allows you to migrate data from one database to another.  You can move database objects from the same kind of database engine or a different database engine. The only requirement is that one of them needs to be an AWS service.

With that out of the way, this is how it works and how I found the experience.

Getting set up

To get the ball rolling you need to set up a replication instance. There are some decisions to make based on your use case that you can read about in the documentation but the console makes this a straightforward process.

You then need to create the endpoints. The source and target data stores can be on an Amazon EC2 instance, and an RDS instance, or an on-premises database.

AWS DMS
Image Source: AWS Documentation

Create a task using the UI

The good thing about using this service is that you can use the UI to run your tasks. This part requires you to specify the database endpoints and a description.

AWS DMS
Image from AWS Documentation

Limitations

  • While the UI is user friendly you need to create these one at a time and can’t clone them to repeat the process if you have multiple tables.

Use the rules

Use the selection rule to isolate which table or group of tables you want to migrate:

AWS DMS

Transform

Use the transformation rules to make sure the data arrives as expected. Options here are to make changes at schema, table and column level.

Limitations

  • I found I was unable to use two transformation rules of the same type. If I used two Table transformation rules sometimes it would pick one of them and sometimes both, resulting in tables that needed to be cleaned up. Not so good when you don’t have the permissions on the database side and need to sheepishly ask your DBA to script a fix.
  • Timeouts were very frustrating at this point. The console would refresh and if you were midway through creating transformation rules you would have to start over.

Create a task

When you select ‘Create task’ the task runs and gives you a progress report from the main screen.

If you make a mistake you can select ‘Modify task’ from the main dashboard screen and rerun the task with your changes.

AWS DMS

Limitations

  • The documentation says that eight tasks should run at once, at max we found that four could run concurrently.
  • There were issues with encoding and dealing with UTF-8 and UTF-16 characters. We got around it by changing the datatype length on the destination column but it was tedious.
  • The documentation sold us on the ability to read ongoing changes from the source database but sadly this is not supported on SQL Server Standard. Keep this in mind if you are not on the Enterprise edition.

Using the Schema Conversion Tool and Database Migration Service was useful. There were a few limitations to be aware of but overall it went smoothly.

Additional resources

Photo by Drigo Diniz from Pexels

Comments are closed, but trackbacks and pingbacks are open.

Bitnami