The Cons of Using AWS DMS with SQL Server
AWS DMS (Database Migration Service) supports ongoing replication to keep your source and target databases synchronized. However there are some pitfalls you should be aware of before you use it in production.
This post lists some of the issues you may run into with an ongoing DMS task that keeps a target database in sync with a source RDS SQL Server database.
Corrupted Data
You might notice corrupted rows in your target database - where some rows have values from other rows. This happens when DMS tries to replicate nvarchar(max) columns.
An easy way to identify this is to set up a separate full load DMS task to sync the same data to a set of reference target tables. After this task completes, the original target tables (which are replicating). If everything is fine, these two sets should match.
If you notice any discrepancy, a short term fix is to do a periodic reload of the target tables. However, not all data will available during the reload. Also, this is not a feasible solution if your target table has foreign key references.
If you really need ongoing replication, you need to convert the nvarchar(max) columns on the source to fixed length columns. This will require recreating constraints, indexes and statistics which use those…
