Sql server database which is on AWS RDS, performing some UPDATETEXT operations on TEXT data types to change some part of TEXT.
At the same time, we want to perform a real-time data replication for the database, and we enable the CDC and logging with the below:
EXEC sys.sp_cdc_enable_db and EXEC sys.sp_cdc_enable_table
When we enable the above, and perform the UPDATETEXT operation, we get the following error:
-Msg 7137, Level 16, State 1, Line 20 UPDATETEXT is not allowed because the column is being processed by a concurrent snapshot or is being replicated to a non-SQL Server Subscriber or Published in a publication allowing Data Transformation Services (DTS) or tracked by Change Data Capture.
Our Limitations are: Cannot change Code (have to use UPDATETEXT). Cannot change data type (have to use TEXT) and Have to perform real time replication. Source DB is AWS RDS SQL Server. Target DB is also AWS RDS SQL Server.
Both Oracle Golden Gate and AWS DMS are based on CDC and Supplemental Logging, where UPDATETEXT is not supported
There could be an option to create a Trigger, capturing DMLs from source and put into another copy of table from there we replicate the data, but this looks to be complex.
Any suggestion/ solution please.
Thanks