Project Aurora (4 Part Series)
I've been working on database migration, it's going surprisingly smoothly given all the moving parts. 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.
AWS Database Migration Service (AWS DMS) is a web service that allows you to migrate data from one database to another. In AWS world these are called endpoints. 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.
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 EC2 instance, an RDS instance, or an on-premises database. This can all be done using the wizard from the AWS console.
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.
Image from AWS Documentation
- 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 Selection rule to isolate which table or group of tables you want to migrate:
Use the Transformation rules to make sure the data lands as expected. Options here are to make changes at Schema, Table and Column level
- 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.
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.
- 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.
This post first appeared on helenanderson.co.nz