Database Migration Service (DMS)
AWS provides an easy way to migrate one database to another whether they are on-site or on AWS RDS. If you are planning to migrate over to AWS, this service will be very handy. I have tested DMS (although not extensively) and I’d like to remark on few findings. AWS DMS Getting Started guide is well documented and is a good place to start if you would like to try it on your on.
The first step of migrating a database using DMS is creating a replication instance. This instance is going to do all the work of actual migration, so you should choose an Instance Class with enough horsepower. I would suggest choosing at least dms.t2.small for the Instance Class, but dms.t2.medium is preferable. Make sure Publicly Accessible is checked if the replication instance needs to access on-site database over the internet.
The second step is creating endpoints which are your source and your destination. Server name is your database server’s IP address or domain name (i.e. 184.108.40.206 or mydb.mydomain.com). After filling out all the required fields click on Run test to see if your configuration is correct. If you get an error make sure your database is allowing connection from the internet or your replication instance’s IP address. In case of AWS RDS endpoint, the RDS has to be publicly accessible.
The last step is creating a task which specifies how you want to migrate data. Most options are self-explanatory, so I’d like to point out something about LOB columns option. I initially ignored this option and either some of my data did not migrate over or the task did not complete. Apparently varchar(max) data types are considered LOBs. Don’t include LOB columns option will just skip over LOB columns and not migrate them. This is the fastest migration option. Full LOB mode is the opposite. The task migrates LOB data in chunks which significantly slows down the migration process. Limited LOB mode is the middle ground. With Limited LOB, you set the Maximum LOB Size. Any data larger than that will be truncated. Try running queries like the following to see what LOB size you’d need.
SELECT MAX(DATALENGTH(myfield)) FROM mytable
In MySQL (characters):
SELECT MAX(LENGTH(myfield)) FROM mytable
In MySQL (BLOB):
SELECT MAX(OCTET_LENGTH(myfield)) FROM mytable
They will return largest data in your field in bytes. You can divide it by 1024 to get the size in kilobytes.
Once you create the task, let it run and track its progress. Table Statistics tab will let you know which tables have been completed, which tables are being processed, and how many rows have been processed.
I have tested DMS by migrating from on-site MSSQL database, which had 18 tables with the largest table containing over 2 million rows, to AWS RDS on a 10Mb/10Mb connection using a dms.t2.medium instance class and Limited LOB option with Maximum LOB Size of 16 kb. The whole process took about 25 minutes.
What doesn’t migrate
Indexes and primary keys are retained. Views, stored procedures, and functions are not migrated. However, these should be fairly simple to transfer over to the new database.
Also, make sure your replication instance is terminated and deleted after replication task completes. It will continue to incur charges if it’s active even if no work is being done by it.