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?
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.
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.
- 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:
Use the Transformation rules to make sure the data arrives 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.
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.
- 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.
Photo by Pixabay on Pexels