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:
SET SHAPE_2 = SHAPE.STAsText()
Where SHAPE_2 = NVARCHAR(MAX)
then application repctl sends this
declare @p1 int
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
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:
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.