957
Chapter 41: Data Change Tracking and Capture
41
■ (^) @filegroup_name: The fi legroup the CDC will be stored on. If not specifi ed, then
the change table is created on the default fi legroup.
■ (^) @allow_partition_switch: Allows ALTER TABLE...SWITCH PARTITION on CDC
table.
If you were following along for the examples in the Change Tracking section of this chapter please note that you will
need to drop and re-create your AdventureWorks database before attempting the examples here in the Change Data
Capture section.
The following batch confi gures CDC to track changes made to the HumanResources
.Department table:
EXEC sys.sp_cdc_enable_table
@source_schema = 'HumanResources',
@source_name = 'Department',
@role_name = null;
With the fi rst table that’s enabled, SQL Server generates two SQL Agent jobs:
■ (^) cdc.dbname_capture
■ (^) cdc.dbname_cleanup
With every table that’s enabled for CDC, SQL Server creates a change table. In addition,
these three tables are created when CDC is enabled:
■ (^) cdc.change_tables
■ (^) cdc.index_columns
■ (^) cdc.captured_columns
For an excellent article on tuning the performance of CDC under various loads, see http://msdn.microsoft
.com/en-us/library/dd266396.aspx.
Working with Change Data Capture
It isn’t diffi cult to work with change data capture. The trick is to understand the transac-
tion log’s log sequence numbers.Assuming AdventureWorks2012 has been freshly installed, the following scripts make
some data changes, so there will be some activity in the log for CDC to gather:c41.indd 957c41.indd 957 7/31/2012 10:17:21 AM7/31/2012 10:17:21 AM
http://www.it-ebooks.info