Phantom Update on Aurora Postgres from AWS DMS Replication from SQL Server Source
We have an EC2 instance with SQL Server installed and AWS DMS Replication set up to sync changes from Database A - Table A to an Aurora Postgres Database A Table A. This has been working fine for the last year for inserts/updates/deletes. We recently added a new field (LastUpdateDate) into Table A in SQL Server of type datetimeoffset, this was automatically added to the corresponding Table A in Aurora Postgres of type varchar(34). This works fine for insert and updates. However if the column LastUpdateDate is NULL and we delete this row on the source (SQL Server), an error is throw in DMS where it is attempting to do an update of the corresponding row in Aurora Postgres but trying to set the LastUpdateDate to an invalid date (10000-01-01 04:17:59.0000000 +04:18), this is 35 characters long and so fails to insert into the LastUpdateField which is on 34 characters. My question is we are only doing a delete, where is this update coming from? Where is this strange date coming from? It is not occurring on the SQL Server side as we have no triggers on the table, have cdc enabled and can see that only a Delete operation was done. The delete operation does succeed but we still have these update errors showing up in our 'public.awsdms_apply_exceptions table.'
Any help would be appreciated. Thanks
I understand that, after the DDL ALTER TABLE .. ADD LastUpdateDate DATETIMEOFFSET operation, when you DELETE a record from the source table DMS is logging an UPDATE exception in the awsdms_apply_exceptions table with an error message like "..source column(s) contain data that exceed(s) the size limit of the corresponding target column ... source column is 35 characters while target column is 34 characters" even though the DELETE is successfully replicated.
The behavior seems like a bug and I have raised a bug report with the Service team. For now, you can safely ignore this specific exception in the awsdms_apply_exceptions table.
Thank you for the reply and raising the bug.
AWS DMS doing a SELECT on TEXT column during continuous replicationasked 2 months ago
DMS CDC for SQL Server - ReplicationAccepted AnswerEXPERTasked 2 years ago
Very Large ASYNC_NETWORK_IO on SQL Server when migrating data from SQL Server EC2 to Aurora Postgres using DMSasked 4 months ago
Phantom Update on Aurora Postgres from AWS DMS Replication from SQL Server Sourceasked 3 months ago
about endpoint connectionasked 5 days ago
AWS DMS performance degradation until stopped and resumedasked a month ago
What happens to a DMS job when a SQL Server AG fails over?Accepted Answerasked 2 years ago
Issues with ongoing loads/replication/delta loads using DMSAccepted Answerasked 3 years ago
DMS to migrate SQL Server from RDS to EC2asked 2 years ago
Is it possible to use AWS RDS SQL Server as an AAG target from on premise primary?asked 7 months ago