UPDATEs to a Primary Key/Unique Index that affect multiple rows may cause conflicts when applying the changes to the target. This may occur, for example, if the UPDATEs are applied to the target as INSERT/DELETE operations rather than a single UPDATE operation. In such a scenario (where UPDATEs to a Primary Key/Unique Index affect multiple rows), working in Batch optimized apply Change Processing mode is not supported as it may result in the table being ignored. Working in Transactional Apply Change Processing mode however, may result in constraint violations. If this happens, you either need to reload the relevant table or locate the problematic records in the Apply exceptions Control Table and edit them manually in the target database. See also: Change Processing Tuning.
I have a customer that currently runs a script that sometimes does multiple deletes and inserts to update the primary key, yet we haven't seen any conflicts with applying changes to the target. The task has been run in both Transactional apply mode and Batch apply mode yet no conflicts or constraint violations have been seen so far. Also it doesn't look like the table is being ignored. The task is going from Microsoft SQL Server to Microsoft SQL server.
However this is a new task, and maybe that's why no issues have shown so far. Why aren't there any conflicts occurring or table being ignored? Would it be recommended to follow the limitation and avoid doing "DELETE/INSERT" to update a primary key on a record? Thank you.
Based on my research, it appears that this is a documentation error. Could you please open a support ticket? I would like to collaborate with R&D to update the User Guide with the following corrections:
The limitation should NOT be mentioned in the SQL Server endpoint section; it should be included in the MS-CDC SQL Server endpoint chapter.
The "deferred update" does not apply to the Primary Key (PK).
Thanks,
John.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Hmm, I think this line refers to Replicate target handling, not MS SQL source endpoint configurations?
if the UPDATEs are applied to the target as INSERT/DELETE operations rather than a single UPDATE operation.
I think this refers to "Apply Conflicts Handling Policy" - "No record found for applying an UPDATE"
With that set to "INSERT the missing target record" any update will be split into a delete first and an insert next. That's all pre-and post update PK based.
I could certainly envision conflicts when PK changes in a single batch interact. For example if one where to 'shuffle down' after a delete. Say you have a source application with rows keyed 9001,9002,9003,9004. Now 9002 is deleted, and the application updates 9003 to become 9002 and 9004 to become 9003. To envision what replicate might do in split mode certainly makes my head hurt and might just confuse Replicate also. I'm not saying it would not work, just saying that would seem challenging to get correct.
