Tuesday, May 03, 2011

Change Tracking Vs. Change Data Capture ( CDC ) in SQL Server 2008


Here we find the difference between the Change Track vs Change Data Capture in SQL Server 2008. Both helps in tracking the table changes and security compliance.

Change Track Change Data Capture – CDC
It is about fact:
It captures only the fact as the tracking table has changed. It does NOT capture the data.
Therefore, change tracking is more limited in the historical questions it can answer compared to change data capture. However, for those applications that do not require the historical information, there is far less storage overhead because of the changed data not being captured
It is about the Data:
Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed.

Internal tables are placed on the same filegroup as the parent entity. You could use the sys.internal_tables catalog view to show all the internal tables and parent entities. For example:
select name, object_name(parent_id) as parent_object from sys.internal_tables

When change data capture is enabled for a database, a few things are added to the database, including a new schema (called cdc), some metadata tables, and a trigger to capture Data Definition Language (DDL) events.

The two function names are, respectively, fn_cdc_get_all_changes_ and fn_cdc_get_net_changes_, with the capture instance name appended. Note that (like the change tracking feature) this functionality requires the table to have a primary key or other unique index.
Supported on “Simple” recovery model also.
It is recommended that you use snapshot isolation when change tracking is enabled. Snapshot isolation itself can add significant workload overhead and requires much more careful management of tempdb.
Prevents Log truncation.
Forces full logging of some bulk operations.

One major point to note here is that once change data capture is enabled, the transaction log behaves just as it does with transactional replication—the log cannot be truncated until the log reader has processed it. This means a checkpoint operation, even in SIMPLE recovery mode, will not truncate the log unless it has already been processed by the log reader.
It uses synchronous tracking mechanism.
once a database is enabled for change tracking, a version number is instituted, which allows ordering of operations
Change Data Capture (CDC) uses the asynchronous process that reads the transaction log.
Change Tracking has minimal impact on the system. It has almost nil impact as it asynchronous mechanism reads from the transaction log.
It uses TempDB heavily It uses transaction log.
DDL Restriction:
There are restrictions on the DDL that can be performed on a table being tracked. The most notable restriction is that the primary key cannot be altered in any way. The other restriction worth calling out here is that an ALTER TABLE SWITCH will fail if either table involved has change tracking enabled.
No such DDL restriction
SQL Agent not needed It requires SQL Agent to be running.
SQL Agent Job & Transaction Replication:
Two SQL Agent jobs may be created: the capture job and the cleanup job. I say "may be created" because the capture job is the same as the one used for harvesting transactions in transactional replication.
If transactional replication is already configured, then only the cleanup job will be created and the existing log reader job will also be used as the capture job
Permission required to enable: SYSADMIN Permission required to enable: DBOwner



Comparing Change Data Capture and Change Tracking

Tracking Changes in your Enterprise

No comments: