AWS DMS and its Limitations

12 min readJan 21, 2022

What is AWS DMS ?

AWS Database Migration Service (AWS DMS) is a cloud service that makes it easy to migrate relational databases, data warehouses, NoSQL databases, and other types of data stores. You can use AWS DMS to migrate your data into the AWS Cloud or between combinations of cloud and on-premises setups.

With AWS DMS, you can perform one-time migrations, and you can replicate ongoing changes to keep sources and targets in sync. If you want to migrate to a different database engine, you can use the AWS Schema Conversion Tool (AWS SCT) to translate your database schema to the new platform. You then use AWS DMS to migrate the data

Issues with DMS

Before using AWS DMS, the most important thing is to analyze whether the version and edition of the source and target destinations are supported by DMS or not for both full load and CDC. Apart from these, few DB versions are supported by only certain versions of DMS. Hence, this should be checked as well. Just as an example, if you want to enable CDC on a SQL Server 2012 Standard edition using AWS DMS, then that is not supported by AWS DMS. So, this is a perfect example where DMS will not work.

Here are the versions and editions of the different databases supported by DMS for different databases :-

Version and edition limitation for SQL Server Database as a source (on premise)

Version and edition limitation SQL Server Database as a source (Amazon RDS)

SQL Server DMS Version Support as a source:- Support for Microsoft SQL Server version 2019 as a source is available in AWS DMS versions 3.3.2 and later

Version and edition limitation SQL Server as a target

You can migrate data to Microsoft SQL Server databases using AWS DMS. With an SQL Server database as a target, you can migrate data from either another SQL Server database or one of the other supported databases.

For on-premises and Amazon EC2 instance databases, AWS DMS supports SQL Server versions 2005, 2008, 2008R2, 2012, 2014, 2016, 2017, and 2019. The Enterprise, Standard, Workgroup, Developer, and Web editions are supported by AWS DMS.

For Amazon RDS instance databases, AWS DMS supports SQL Server versions 2008R2, 2012, 2014, 2016, 2017, and 2019. The Enterprise, Standard, Workgroup, Developer, and Web editions are supported by AWS DMS.

Version and edition limitation SQL Server DMS Version Support as a target:-

Support for Microsoft SQL Server version 2019 as a target is available in AWS DMS versions 3.3.2 and later.

Version and edition limitation for Oracle

AWS DMS supports the following Oracle database editions:

Oracle Enterprise Edition
Oracle Standard Edition
Oracle Express Edition
Oracle Personal Edition

Version and edition limitation for Oracle as a source (on premise)

For self-managed Oracle databases, AWS DMS supports all Oracle database editions for versions 10.2 and later (for versions 10.x), 11g and up to 12.2, 18c, and 19c.

Version and edition limitation for Oracle RDS as a source

For Amazon RDS for Oracle databases that AWS manages, AWS DMS supports all Oracle database editions for versions 11g (versions 11.2.0.4 and later) and up to 12.2, 18c, and 19c.

Oracle DMS Version Support as a source :- Support for Oracle version 19c as a source is available in AWS DMS versions 3.3.2 and later. Support for Oracle version 18c as a source is available in AWS DMS versions 3.3.1 and later.

Version and edition limitation for Oracle as a target

AWS DMS supports Oracle versions 10g, 11g, 12c, 18c, and 19c for on-premises and EC2 instances for the Enterprise, Standard, Standard One, and Standard Two editions as targets. AWS DMS supports Oracle versions 11g (version 11.2.0.3.v1 and later), 12c, 18c, and 19c for Amazon RDS instance databases for the Enterprise, Standard, Standard One, and Standard Two editions.

Oracle DMS Version Support as a target :- Support for Oracle version 19c as a target is available in AWS DMS versions 3.3.2 and later. Support for Oracle version 18c as a target is available in AWS DMS versions 3.3.1 and later.

Version and edition limitation for MySQL as a source (on premise, Aurora MySQL and MySQL RDS)

You can migrate data from any MySQL-compatible database (MySQL, MariaDB, or Amazon Aurora MySQL) using AWS Database Migration Service. MySQL versions 5.5, 5.6, 5.7, and 8.0. MariaDB versions 10.0.24 to 10.0.28, 10.1, 10.2, 10.3, 10.4, and 10.5, and also Amazon Aurora MySQL, are supported for on-premises.

Version and edition limitation for MySQL DMS Version Support as a source

Support for MySQL 8.0 as a source is available in AWS DMS versions 3.4.0 and later, except when the transaction payload is compressed.

Version and edition limitation for MySQL as a target

You can migrate data to any MySQL-compatible database using AWS DMS, from any of the source data engines that AWS DMS supports. If you are migrating to an on-premises MySQL-compatible database, then AWS DMS requires that your source engine reside within the AWS ecosystem. The engine can be on an AWS-managed service such as Amazon RDS, Amazon Aurora, or Amazon S3. Or the engine can be on a self-managed database on Amazon EC2.

AWS DMS supports versions 5.5, 5.6, 5.7, and 8.0 of MySQL and Aurora MySQL

You can use the following MySQL-compatible databases as targets for AWS DMS:

MySQL Community Edition
MySQL Standard Edition
MySQL Enterprise Edition
MySQL Cluster Carrier Grade Edition
Amazon Aurora MySQL

MySQL DMS Version Support as a target

Support for MySQL 8.0 as a target is available in AWS DMS versions 3.3.1 and later.

Version and edition limitation for PostgreSQL as a source

You can migrate data from one or many PostgreSQL databases using AWS DMS. With a PostgreSQL database as a source, you can migrate data to either another PostgreSQL database or one of the other supported databases. AWS DMS supports a PostgreSQL version 9.4 and later (for versions 9.x), 10.x, 11.x, 12.x, and 13.x (for PREVIEW use only) database as a source for these types of databases:

On-premises databases
Databases on an Amazon EC2 instance
Databases on an Amazon RDS DB instance
Databases on an DB instance based on Amazon Aurora PostgreSQL-Compatible Edition

Version and edition limitation for PostgreSQL as a target

You can migrate data to PostgreSQL databases using AWS DMS, either from another PostgreSQL database or from one of the other supported databases. AWS DMS supports a PostgreSQL version 9.4 and later (for versions 9.x), 10.x, 11.x, 12.x, and 13.x (for PREVIEW use only) database as a target for these types of databases:

On-premises databases
Databases on an EC2 instance
Databases on an Amazon RDS DB instance
Databases on an Amazon Aurora DB instance with PostgreSQL compatibility

Issues with DMS because of data related issues

Not all the constraints on the data in different databases, different types of tables, different types of columns and procedures can be migrated using AWS DMS. So, AWS DMS will not work in these scenarios as well. Here are the limitations for each database :-

Limitations with SQL Server as a source

The following limitations apply when using a SQL Server database as a source for AWS DMS:

The identity property for a column isn’t migrated to a target database column.
The SQL Server endpoint doesn’t support the use of sparse tables.
Windows Authentication isn’t supported.
Changes to computed fields in a SQL Server aren’t replicated.
Temporal tables aren’t supported.
SQL Server partition switching isn’t supported.
When using the WRITETEXT and UPDATETEXT utilities, AWS DMS doesn’t capture events applied on the source database.
The following data manipulation language (DML) pattern isn’t supported.

SELECT * INTO new_table FROM existing_table

When using SQL Server as a source, column-level encryption isn’t supported.
Transparent Data Encryption (TDE) enabled at the database level is supported.
AWS DMS doesn’t support server level audits on SQL Server 2008 or SQL Server 2008 R2 as sources. This is because of a known issue with SQL Server 2008 and 2008 R2. For example, running the following command causes AWS DMS to fail.

USE [master]

ALTER SERVER AUDIT [my_audit_test-20140710] WITH (STATE=on)

Geometry columns are not supported in full lob mode when using SQL Server as a source. Instead, use limited lob mode or set the InlineLobMaxSize task setting to use inline lob mode.
A secondary SQL Server database isn’t supported as a source database for ongoing replication (CDC) tasks.
When using a Microsoft SQL Server source database in a replication task, the SQL Server Replication Publisher definitions are not removed if you remove the task. A Microsoft SQL Server system administrator must delete those definitions from Microsoft SQL Server.
Replicating data from indexed views isn’t supported.
Renaming tables using sp_rename isn’t supported (for example, sp_rename ‘Sales.SalesRegion’, ‘SalesReg;)
Renaming columns using sp_rename isn’t supported (for example, sp_rename ‘Sales.Sales.Region’, ‘RegID’, ‘COLUMN’;)
TRUNCATE events aren’t captured.
With SQL Server 2012 and SQL Server 2014, when using DMS replication with Availability Groups, the distribution database can’t be placed in an availability group. SQL 2016 supports placing the distribution database into an availability group, except for distribution databases used in merge, bidirectional, or peer-to-peer replication topologies.

The following limitations apply when accessing the backup transaction logs:

Encrypted backups aren’t supported.
Backups stored at a URL or on Windows Azure aren’t supported.

The following limitations apply when accessing the backup transaction logs at file level:

The backup transaction logs must reside in a shared folder with the appropriate permissions and access rights.
Active transaction logs are accessed through the Microsoft SQL Server API (and not at file-level).
Compressed backup transaction logs aren’t supported.
UNIX platforms aren’t supported.
Reading the backup logs from multiple stripes isn’t supported.
Microsoft SQL Server backup to multiple disks isn’t supported.
When inserting a value into SQL Server spatial data types (GEOGRAPHY and GEOMETRY), you can either ignore the spatial reference system identifier (SRID) property or specify a different number. When replicating tables with spatial data types, AWS DMS replaces the SRID with the default SRID (0 for GEOMETRY and 4326 for GEOGRAPHY).
If your database isn’t configured for MS-REPLICATION or MS-CDC, you can still capture tables that do not have a Primary Key, but only INSERT/DELETE DML events are captured. UPDATE and TRUNCATE TABLE events are ignored.
Columnstore indexes aren’t supported.
Memory-optimized tables (using In-Memory OLTP) aren’t supported.
When replicating a table with a primary key that consists of multiple columns, updating the primary key columns during full load isn’t supported.
Delayed durability isn’t supported.
The readBackupOnly=Y endpoint setting (ECA) doesn’t work on Amazon RDS SQL Server source instances because of the way RDS performs backups.
EXCLUSIVE_AUTOMATIC_TRUNCATION doesn’t work on Amazon RDS SQL Server source instances because RDS users don’t have access to execute the SQL Server stored procedure, sp_repldone.

This source can be referred for detailed list of limitations :- https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html

Limitations with SQL Server as target

The following limitations apply when using a SQL Server database as a target for AWS DMS:

When you manually create a SQL Server target table with a computed column, full load replication is not supported when using the BCP bulk-copy utility. To use full load replication, disable the Use BCP for loading tables option on the Advanced tab on the AWS Management Console. For more information on working with BCP, see the Microsoft SQL Server documentation.
When replicating tables with SQL Server spatial data types (GEOMETRY and GEOGRAPHY), AWS DMS replaces any spatial reference identifier (SRID) that you might have inserted with the default SRID. The default SRID is 0 for GEOMETRY and 4326 for GEOGRAPHY.
Temporal tables are not supported. Migrating temporal tables may work with a replication-only task in transactional apply mode if those tables are manually created on the target.
Currently, boolean data types in a PostgreSQL source are migrated to a SQLServer target as the bit data type with inconsistent values. As a workaround, precreate the table with a VARCHAR(1) data type for the column (or let AWS DMS create the table). Then have downstream processing treat an “F” as False and a “T” as True.
DMS doesn’t support bring your own license (BYOL) for Microsoft SQL Server

This source can be referred for detailed list of limitations :- https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.SQLServer.html

Limitations with MySQL as a source

When using a MySQL database as a source, consider the following:

Change data capture (CDC) isn’t supported for Amazon RDS MySQL 5.5 or lower. For Amazon RDS MySQL, you must use version 5.6 or 5.7 to enable CDC. CDC is supported for self-managed MySQL 5.5 sources.
For CDC, CREATE TABLE, ADD COLUMN, and DROP COLUMN changing the column data type, and renaming a column are supported. However, DROP TABLE, RENAME TABLE, and updates made to other attributes, such as column default value, column nullability, character set and so on, are not supported.
For partitioned tables on the source, when you set Target table preparation mode to Drop tables on target, AWS DMS creates a simple table without any partitions on the MySQL target. To migrate partitioned tables to a partitioned table on the target, precreate the partitioned tables on the target MySQL database.
Using an ALTER TABLE table_name ADD COLUMN column_name statement to add columns to the beginning (FIRST) or the middle of a table (AFTER) isn’t supported. Columns are always added to the end of the table.
CDC isn’t supported when a table name contains uppercase and lowercase characters, and the source engine is hosted on an operating system with case-insensitive file names. An example is Microsoft Windows or OS X using HFS+.
You can use Aurora MySQL-Compatible Edition Serverless for full load, but you can’t use it for CDC. This is because you can’t enable the prerequisites for MySQL. For more information, see Parameter groups and Aurora Serverless v1.
The AUTO_INCREMENT attribute on a column isn’t migrated to a target database column.
Capturing changes when the binary logs aren’t stored on standard block storage isn’t supported. For example, CDC doesn’t work when the binary logs are stored on Amazon S3.
AWS DMS creates target tables with the InnoDB storage engine by default. If you need to use a storage engine other than InnoDB, you must manually create the table and migrate to it using do nothing mode.
You can’t use Aurora MySQL read replicas as a source for AWS DMS unless your DMS migration task mode is Migrate existing data — full load only.
If the MySQL-compatible source is stopped during full load, the AWS DMS task doesn’t stop with an error. The task ends successfully, but the target might be out of sync with the source. If this happens, either restart the task or reload the affected tables.
Indexes created on a portion of a column value aren’t migrated. For example, the index CREATE INDEX first_ten_chars ON customer (name(10)) isn’t created on the target.
In some cases, the task is configured to not replicate LOBs (“SupportLobs” is false in task settings or Don’t include LOB columns is chosen in the task console). In these cases, AWS DMS doesn’t migrate any MEDIUMBLOB, LONGBLOB, MEDIUMTEXT, and LONGTEXT columns to the target.
BLOB, TINYBLOB, TEXT, and TINYTEXT columns aren’t affected and are migrated to the target.
Temporal data tables or system — versioned tables are not supported on MariaDB source and target databases.
If migrating between two Amazon RDS Aurora MySQL clusters, the RDS Aurora MySQL source endpoint must be a read/write instance, not a read replica instance.
AWS DMS currently doesn’t support compressed transaction log payloads introduced in MySQL 8.0.20.
AWS DMS currently doesn’t support views migration for MariaDB.
AWS DMS doesn’t support DDL changes for partitioned tables for MySQL.
AWS DMS doesn’t currently support XA transactions.

Limitations with MySQL as a target

When using a MySQL database as a target, AWS DMS doesn’t support the following:

The data definition language (DDL) statements TRUNCATE PARTITION, DROP TABLE, and RENAME TABLE.
Using an ALTER TABLE table_name ADD COLUMN column_name statement to add columns to the beginning or the middle of a table.
When only the LOB column in a source table is updated, AWS DMS doesn’t update the corresponding target column. The target LOB is only updated if at least one other column is updated in the same transaction.
When loading data to a MySQL-compatible target in a full load task, AWS DMS doesn’t report duplicate key errors in the task log.
When you update a column’s value to its existing value, MySQL-compatible databases return a 0 rows affected warning. Although this behavior isn’t technically an error, it is different from how the situation is handled by other database engines. For example, Oracle performs an update of one row. For MySQL-compatible databases, AWS DMS generates an entry in the awsdms_apply_exceptions control table and logs the following warning.

Some changes from the source database had no impact when applied to the target database. See awsdms_apply_exceptions table for details.

References/Online Resources Used

https://docs.aws.amazon.com/dms/latest/userguide/