By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

AWS DMS doing a SELECT on TEXT column during continuous replication

0

We have a Table A with a DMS task setup to do continuous replication on this from the source database (SQL Server Instance on EC2) to target database (Aurora Postgres). This table has 10 columns, one of which is of type text (I know this bad), we will call this column, 'INSTRUCTIONS'. We also monitor blocking on this table and on occasions we can see blocking issues arise where our DMS user is being blocked trying to run the following query: SELECT Instructions FROM dbo.TableA Where ID = @p1 The blocking is not the issue, what I'm wondering is why is DMS querying the table directly, should it not be using transactional replication and reading from the T-Log. Any help on this would be appreciated as I cannot see any mention of DMS reading from the table in replication mode in the docs. Thanks.

Topics
Migration & Modernization
Tags
AWS Database Migration Service
Language
English
AWS-User-0018211
asked 2 years ago701 views
1 Answer
0

this is expected behaviour. During continuous replication or rather CDC DMS will lookup transactions in source tlog. Transactions monitored are DMLS/DDLS pertaining to tables in scope. Please note SQL server wont log the the actual lob column data in tlog as part of the transaction. Thus every insert update delete captured in active tlog by DMS will trigger a lob lookup on source required to be applied to target.

AWS
subhashr
answered 2 years ago
  • AWS-User-0018211
    2 years ago

    I appreciate the reply, but I'm wondering about the line you have 'SQL Server wont log the actual lob column data in the tlog'? If that actual lob data is not stored in the transaction log, then how can you restore a SQL Server database to a point in time using the transaction log if you are saying that lob data is not stored in the transaction log? I understand if you use the write clause for example in an UPDATE that this a minimally logged but this is only a specific scenario. For example if I have a Table A in my Database A I which has one column of type text. If i perform an insert on that table, it will get written to the log, take a log backup, then delete that row from the table, If I restore the log backup I just took it will contain the text column, if it is not actually stored in the tlog then where would it be able to get this data from? Many Thanks.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content