For database administrators and engineers, migrating a database can be a major headache. It’s such a headache that it actually prohibits some teams from migrating to AWS’ Relational Database Service (RDS), even though doing so would save them time and money in the long run.
Imagine you’re a DBA for Small Business Y and you want to manage your data in RDS, but you have three terabytes of data with a ton of tables, foreign keys and many dependencies. Back in the day, migrating your SQL Server database to RDS might have looked something like this:
- Coordinate with Product Leads to find a time when your business can handle up to a 24-hour outage of source database.
- Dump all the existing data into a backup.
- Restore the data on an intermediary EC2 SQL Server instance.
- Connect to the new RDS instance.
- Generate metadata script from the source or intermediary instance.
- Execute metadata script on target RDS instance.
- Migrate the data to the RDS instance using SQL Server’s Import tool.
- Optional: Encounter complications such as import failures, loss of metadata integrity, loss of data, and extremely slow import speeds.
- Cry a lot and then sleep for days.
Enter AWS Database Migration Service. This new tool from AWS allows DBAs to complete migrations to RDS, or even to a database instance on EC2, with minimal steps and minimal downtime to the source database. What does that mean for you? No 2AM migrations and no tears.
Migrations with AWS DMS have three simple steps:
- Provision a replication instance
- Define source and target endpoints
- Create one or more tasks to migrate data between source and target
The service automates table-by-table migration into your target database without having to use any backups, dump files, or manually administering an intermediary database server. In addition, you can use the service to continue replicating changes from the source environment until you are ready to cutover. That means your application’s downtime will be just a few minutes instead of several hours or even days.
Another great feature of DMS is that you can watch the progress of each table’s migration in the AWS console. The dashboard allows users to see, in real time, how the migration is going and see exactly where any breakages occur.
If you are planning to use the AWS Database Migration Service soon, here are a few tips to streamline your process:
- In your target instance, set up your table structure prior to migrating the data. The service will not automatically set up your foreign keys and indexes. Set up your metadata from AWS schema conversion tool (or simple mysqldump). Make sure to use truncate option during schema import so that the tables you create aren’t wiped.
- If you think you may have extra-large LOBs, use Full LOB Mode to avoid data truncation.
- Additional best practices for using DMS can be found here.
Enjoy!
-Trish Clark, Oracle DBA
