November 18, 2020 at 9:24 am
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:
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.
November 18, 2020 at 9:25 am
sending a single update works ok
November 20, 2020 at 9:42 pm
Just to confirm - you don't have any triggers on the table, right? Those can cause all sorts of surprises like that.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 21, 2020 at 10:11 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply