Categories
AWS Data Technology

Moving data with the AWS Database Migration Service

The AWS Database Migration Service promises to take a lot of the pain out of migration, here’s how it works at a high level.

I’ve been working on database migration, it’s going surprisingly smoothly given all the moving parts. This week I worked with the AWS Database Migration Service, and it, for the most part, also went smoothly.


What is it? Why do I need it?
Getting set up
Create a Task using the UI
Use the rules to Select
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, 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 to Select

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.

AWS DMS

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.

Overall, using the Schema Conversion Tool and Database Migration Service was useful but it came with a few bugs and limitations. With any luck these will be cleared up and fixed by the time you get to your migration.

I’ll be keeping up with the Documentation as the next migration is never far away.


Additional Resources


Photo by Pixabay on Pexels

By Helen Anderson

I’m passionate about technology and building data applications.