Replication sending same change over and again when doing a batch update

  • Good morning, I recently set up replication on my sql server - Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)

    Mar 18 2018 09:11:49

    Copyright (c) Microsoft Corporation

    Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

    going via AWS DMS, which is then sending the data to a PostgresSQL Aurora database in AWS.  We noticed that when doing a batch update in sql server to a table, i.e. updating the entire table, say putting some text in a column based on another column, the change gets propagated to PostgreSQL but the same value is written to all rows in the database, I have run a trace on SQL Server as I make the batch change and can see something very curious -

    The original query was this:

    UPDATE dbo.LA_Telephones_2

    SET SHAPE_2 = SHAPE.STAsText()

    Where SHAPE_2 = NVARCHAR(MAX)

    then application repctl sends this

    declare @p1 int

    set @p1=100

    exec sp_prepare @p1 output,NULL,N'SELECT cast( [SHAPE] as varchar(max)), [shape_2] FROM [dbo].[LA_Telephones_2] WHERE [SW_MEMBER]=25',1

    select @p1

    for a single row, and then it repeats the above for as many rows as there is in the table - exactly the above (sw_member is an INT primary key) - so it just repeats the same row 25 times (this is a small test table).  The text  in column SHAPE_2 then gets written to EVERY row in the Postgres table... which isn't good.

    I originally thought this was some sort of AWS DMS issue, but have drilled down into it being a replication issue.  I recently deleted my publication and set it up again following DMS instructions to the letter:

    https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.Limitations

    but the same thing is occurring.  Does anyone know why a SQL publisher would send the same data over and again please?  I have done lots of "googling" but cannot find anything to help me, thank you.  Duncan.

     

     

     

  • sending a single update works ok

  • Just to confirm - you don't have any triggers on the table, right?  Those can cause all sorts of surprises like that.

  • Hi Brien, no indeed.  In fact it is a bug with at least the latest 2 versions of DMS, spinning up another instance on a release previous to that, and with everything else the same, fixes the issue.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply