The key to understand SQL Server CDC

I think the most important thing to understand SQL Server CDC is to realized the LSN value in the CDC tables are the commit LSN. Than, it is more easy to understand the the LSN is mapping to the tran_end_time in the cdc.lsn_time_mapping table.

reference:
http://rusanu.com/2012/01/17/what-is-an-lsn-log-sequence-number/

Advertisements

SQL Server CDC Table-Values Functions

Microsoft SQL Server automatically generate table valued functions for the change instance when CDC tracking is enabled on tables.

Unfortunately, these functions may report the misleading error message:

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...

The real meaning of the error message is:

  • one or more arguments are null;
  • the LSN is outside the range of min(start_lsn) and the max(start_lsn) in the cdc.lsn_time_mapping table; or
  • the from_lsn is greather than the to_lsn.

In short the lsn values specifying the range must be existing in the cdc.lsn_time_mapping table.

But, I am using the system function sys.fn_cdc_map_time_to_lsn to map the time to lsn. How come I ended up with a lsn does not exist in the cdc.lsn_time_mapping table? Yes, it will happen. The relational operator “smallest greater than” and “smallest greater than or equal” map the time to a lsn greater the the given time. For example, the max time in the cdc.lsn_time_mapping table is 2017-01-01, the return value will be null if the givn time is later than “2017-01-01”.

It is confusing.

On more thing is the the system function sys.fn_cdc_increment_lsn and sys.fn_cdc_decrement_lsn do not return the next value from the cdc.lsn_time_mapping table. It just returns the next lsn value.

HTTP Entities

HttpClient distinguishes three kinds of entities, depending on where their content originates:

streamed: The content is received from a stream, or generated on the fly. In particular, this category includes entities being received from HTTP responses. Streamed entities are generally not repeatable.

self-contained: The content is in memory or obtained by means that are independent from a connection or other entity. Self-contained entities are generally repeatable. This type of entities will be mostly used for entity enclosing HTTP requests.

wrapping: The content is obtained from another entity.

https://hc.apache.org/httpcomponents-client-ga/tutorial/html/fundamentals.html

TSQL Month End

WITH DATES AS
(
SELECT GETDATE() DT
UNION ALL
SELECT DT + 1
FROM DATES
WHERE DT < GETDATE() + 3000
)
SELECT CAST(DT AS DATE) AS DATE
, MonthEnd = CASE WHEN DAY(DT + 1) = 1 THEN 1 ELSE 0 END
, QuarterEnd = CASE WHEN DATEPART(quarter, DT + 1) != DATEPART(quarter, DT) THEN 1 ELSE 0 END
, YearEnd = CASE WHEN YEAR(DT + 1) != YEAR(DT) THEN 1 ELSE 0 END
FROM DATES
OPTION (MAXRECURSION 3000)

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