SQL Server Deferred Update causes CDC problem

Microsoft introduced a new column in CDC table to address the deferred update problem.
https://support.microsoft.com/en-us/help/3030352/fix-the-change-table-is-ordered-incorrectly-for-updated-rows-after-you
https://blogs.msdn.microsoft.com/sql_server_team/cdc-functionality-may-break-after-upgrading-to-the-latest-cu-for-sql-server-2012-2014-and-2016/

Using the trace flag 8207 to resolve the problem
http://www.c-sharpcorner.com/blogs/resolve-merge-statement-issue-on-cdc-enabled-table

Discussion of CDC problems caused by Deferred Update
https://connect.microsoft.com/SQLServer/Feedback/Details/690476

Microsoft’s explanation on Deferred Update
https://support.microsoft.com/en-us/help/238254/update-statements-may-be-replicated-as-delete-insert-pairs

SQL Server Version List
https://sqlserverbuilds.blogspot.com/

cdc.fn_cdc_get_net_changes_ (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/cdc-fn-cdc-get-net-changes-capture-instance-transact-sql

Remarks:
(1) Microsoft SQL server does not perform deferred updates since SQL Server 2000. It does In-Place Updates and Not-In-Place Updates. “However, if the table has a trigger on it or is marked for replication, the update is still done in place but is recorded in the log as a delete followed by an insert (this provides the before-and-after image for the trigger that is referenced in the inserted and deleted tables).” [Microsoft SQL Server 2012 Unleashed]

Related:
http://www.differencebetween.com/difference-between-deferred-update-and-vs-immediate-update/
https://msdn.microsoft.com/en-us/library/ms715008%28v=vs.85%29.aspx?f=255&MSPPError=-2147217396
Deferred update instead of direct

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s