Relocating a database to AWS makes it easier to manage the infrastructure, provides enhanced scaling options, and offers help with modernization. Yet, moving a database is not just about moving data from one database to another. Differences in Database Engine Type, Networking, Permissions, Schemas, Data types and workload patterns can lead to difficulties during migration.
AWS Database Migration Service (AWS DMS) is designed to migrate a database to AWS with minimum downtime. It offers full data transfer, and afterward, it can continuously transmit data from the original database into the new one.
But even DMS can have migration issues such as connection failures, slow data transfer speeds, missing indexes, duplicate records and other CDC issues.
Knowing the common problems associated with DMS migration can help teams to solve the issues faster and create a reliable migration plan.
What Is AWS DMS?
AWS Database Migration Service (DMS) is a cloud service of AWS that is aimed at assisting in the transfer of data between databases and other storage devices. DMS can be used both for homogeneous migrations, when both source and target are based on the same DB engine, as well as for heterogeneous migrations when different DB engines are being used.
A practical DMS architecture consists of three main components: the source endpoint, the target endpoint, and the Replication Instance.
The Replication Instance is the part that carries out the migration process. It connects to the source, gets the data, manages the migration process and sends the data to the target.
DMS is also capable of capturing changes at the source to replicate them continuously at the target.
Though the architecture is simple, diagnostic troubles may appear in the components.
1. Issues with Connecting to Source or Target
Connection issues are usually one of the first hurdles encountered by teams while migrating to DMS. For a successful migration, DMS must be able to connect to the source or target database. This issue can be caused by incorrect endpoint specifications, incorrect port, incorrect credentials, firewall rules, security groups, network ACLs, routing, or a VPC problem. For Amazon RDS endpoints, AWS recommends that you verify that the endpoint and port information is correct and is reflected in the DMS endpoint for the RDS database that you are using. The same applies to the security group for RDS, which should also allow access from the DMS replication instance. Moreover, you need to check the routing and public accessibility requirements if you are working with a database out of the DMS VPC.
How can it be solved?
First, test the endpoint connection from DMS instead of proceeding to troubleshoot the migration task.
Check database hostname, port, user name, password, and SSL settings if applicable Check replication instance security group and database firewalls Check route tables and network ACLs A successful endpoint connection test is helpful as you know DMS can connect to the endpoint.
2. AWS DMS Replication Instance Is Resource-Constrained
A migration may look good on paper but not really in practice. Migration processes may be slow and inefficient due to resource constraints of the DMS replication instance (insufficient CPU, memory, storage performance or I/O performance). To troubleshoot slow processes, AWS recommends monitoring CPU performance, memory performance, swap usage and IOPS.
The situation is even more critical if many migration processes are being completed at the same time.
For instance, a small replication instance can be able to perform well while migrating a single database, but struggle when moving a variety of large tables and performing CDC processes.
Addressing the issue
Utilize Amazon CloudWatch for monitoring your replica instance for resource constraints.
If you observe that your instance has consistently high memory or CPU utilization, consider choosing a larger instance class.
In cases of I/O-driven workloads, particularly for IOPS provisioning or distribution of workload across different replication instances, better performance can be achieved. It may be beneficial to consider workload distribution when multiple jobs are competing for the same replication resources.
The point is not simply to pick the biggest instance. Rather, the aim is to adjust the replication environment based on the size of the database, the number of tables, the number of transactions, CDC requirements, and the number of concurrent tasks.
The third common problem that may arise is that the migration task appears stalled.
3. The DMS console may show no movement whatsoever.
However, it doesn’t mean that the migration task has stopped.
According to AWS, several parameters determine the progress percentage of a migration task, including the statistics of source database tables.
In situations where reliable estimates about row counts are missing, the percentage that is displayed may be inaccurate.
Therefore, it’s important to not only think about the percentage of progress. Administrators may want to look at the task state, table statistics, CloudWatch metrics and logs to get more detailed information about the progress of the migration. If the number of rows loaded continues to increase, then the migration task is running, although the percentage of progress displayed on the console may not change.
4. Nothing Was Migrated Once the Task Completed
In a highly perplexing situation, a DMS task may report that it has been completed successfully; however, one will find that no expected data has made its way to the destination.
One of the causes of this problem can be insufficient permission levels on the source database.
According to the AWS platform, it is advisable to check whether the user’s profile created for the source endpoint has the necessary read privileges over the tables to be migrated.
Another possible cause can be wrong table mapping settings.
Though the task of migration is believed to be completed successfully, the problem may still arise due to the absence of anything that meets the selection criteria.
How to solve this?
Examine table mappings and selection criteria.
Select schema and tables. Make sure permission is granted to the source DB. Check task logs for any failures/warnings. Also, tables and views can be distinguished because the behavior of DMS depends on the database engine and the migration settings.
5. Lack of Indexes, Foreign Keys, and Constraints
The critical assumption regarding DMS is that every object from the database will be carried over to the target.
According to AWS, DMS only carries the table structure along with primary keys, and sometimes unique indexes, to the target, while there can be some secondary database objects that allow for full duplication of the source schema. Secondary indexes, restrictions on anything other than primary keys, and data default settings may require further consideration.
This means that migration may seem to be successful while some schema aspects may still await post-migration configuration.
While native database tools might work for homogenous migration purposes, the AWS DMS Schema Conversion tool can be of help with heterogeneous migrations.
It is important that, prior to the production cutover, teams validate that the source schema matches the target schema and not simply assume that the successful transmission entails that the entire database has been copied.
6. CDC Is Stuck After Full Load
CDC is used to keep applications operational while moving data from one point to another. In the very first step of CDC, existing data is copied to the target. After that, CDC captures changes in the source and integrates them into the target.
Things can go wrong when CDC doesn’t work as it should. There can be a number of reasons for this depending on the type and configuration of the source engine. Database logs from the source, replication settings, task status, latency metrics, and DMS logs need to be reviewed.
Moreover, large numbers of transactions waiting to be replicated may make it look as though the target is not current, regardless of how replication is working.
Thus, CDC must be checked properly before the migration takes place, not after.
7. Main Key and Repeated Entry Problems
The replication process that follows depends of the proper functioning of key keys.
If tables do not have the right primary keys or unique identifiers, DMS may face problems preventing identification of rows in the course of change processing.
AWS describes cases of duplicated entries for tables serving as targets that do not have primary keys, recommending troubleshooting.
Primary key violations may also be observed in cases of task restart.
Before launching the migration process, it’s best to inspect crucial tables and check the correctness of their keys.
If there are particular limitations in the target structure that could be incompatible with the incoming data, they should be exposed and reviewed while testing, but do not wait for the transfer moment to discover them.
8. Big Object Migration Issues
Large objects or LOBs can complicate the migration process. This may include large text fields, documents, images, or other large binary or character objects. Large object migration may also lead to increased network utilization, resource consumption, and migration time. AWS recommends that tasks should be tailored to LOB workloads in troubleshooting situations. In either case, it’s recommended to test sample tables, rather than rely on results from smaller tables, prior to migrating large LOBs in production.
Final Thoughts
Though AWS DMS can make the migration of databases easier, you must acknowledge that it cannot solve the engineering hurdles associated with moving a production database.
The problems encountered when migrating with DMS mostly relate to connectivity, IAM permissions, replication instance size, long processing time, CDC, missing schema objects, primary key conflicts, LOB speed, lack of support for certain DB versions, and network setup.
In troubleshooting, one must always consider the DB migration in its entirety, not merely as one isolated task performed by DMS.
All aspects of migration have to be audited together, including the source database, destination database, replication instance, network, IAM permissions, migration maps, CloudWatch metrics, etc.
Teams that test, monitor, validate schemas and the rollback plan will be able to identify risks associated with migration before they become serious problems.