-
8/11/2019 Change Data Capture and Change Tracking Deep Dive
1/13
CHANGE DATA CAPTURE
& CHANGE TRACKINGDEEP DIVE
W. Kevin Hazzard
LinchpinPeople.com
Group Principal
-
8/11/2019 Change Data Capture and Change Tracking Deep Dive
2/13
CHANGE DATA CAPTURE
Turn it onEXECUTE sys.sp_cdc_enable_db;
Which generates for the whole database
A capture job
A cleanup job
Then include at least one table withEXECUTE sys.sp_cdc_enable_table ;
Which creates a capture instance containing
A change table
An all-changes function
A net-changes function (optional)
transactions
w
sources
changes
capture
job
ETL
ETL
-
8/11/2019 Change Data Capture and Change Tracking Deep Dive
3/13
CAPTURE INSTANCE
Contains
The capture table
cdc.fn_cdc_get_all_changes_ function
cdc.fn_cdc_get_net_changes_ function (op
Maximum two per source table
-
8/11/2019 Change Data Capture and Change Tracking Deep Dive
4/13
SYS.SP_CDC_ENABLE_TABLE
@source_schema
@source_name
@supports_net_changes
@role_name
@filegroup_name
@index_name
@captured_column_
@allow_partition_sw
@capture_instance
-
8/11/2019 Change Data Capture and Change Tracking Deep Dive
5/13
DEMONSTRATION
Enabling Change Data Capture (CDC)
Creating a CDC Capture Instance
Querying CDC Tables as Things Change
-
8/11/2019 Change Data Capture and Change Tracking Deep Dive
6/13
SQL SERVER AGENT JOBS
cdc.Capture_capture
Starts with SQL Agent
RAISERROR(22801, 10, -1);
EXEC sys.sp_MScdc_capture_job;
Just a wrapper for sys.sp_cdc_scan cdc.Capture_cleanup
Runs at 02:00 daily
EXEC sys.sp_MScdc_cleanup_job;
-
8/11/2019 Change Data Capture and Change Tracking Deep Dive
7/13
-
8/11/2019 Change Data Capture and Change Tracking Deep Dive
8/13
HANDLING SCHEMA CHANGES
Deleted and new columns are handled well
Modified columns require specific steps:
Stop the capture and cleanup jobs
Change the schema as necessary
Generate new capture instances for all modified tables
Process all data in the old and new capture instances Manually run the cleanup job
Delete the old capture instance
Turn the capture and cleanup jobs back on
-
8/11/2019 Change Data Capture and Change Tracking Deep Dive
9/13
CHANGE TRACKING
Turn it on for one database
ALTER DATABASE SET
CHANGE_TRACKING = ON;
Then enable a table
ALTER TABLE ENABLE
CHANGE_TRACKING;
transactionsw
sources ETL
sync
-
8/11/2019 Change Data Capture and Change Tracking Deep Dive
10/13
CHANGE TRACKING TABLES & FUNCT
sys.change_tracking_databases
sys.change_tracking_tables
CHANGETABLE(CHANGES)
CHANGETABLE(VERSION)
CHANGE_TRACKING_CURRENT_VERSION()
CHANGE_TRACKING_MIN_VALID_VERSION()
CHANGE_TRACKING_IS_COLUMN_IN_MASK()
WITH CHANGE_TRACKING_CONTEXT
-
8/11/2019 Change Data Capture and Change Tracking Deep Dive
11/13
DEMONSTRATION
Enabling Change Tracking (CT) for a Database
Enabling CT on a Table
Querying CT Tables and Functions as Things Change
-
8/11/2019 Change Data Capture and Change Tracking Deep Dive
12/13
HANDLING SCHEMA CHANGES
No modifications to the primary key are allowed including related indexes
Dropping columns is OK but they may still appear in the change data
When adding columns, changes are tracked but the metadata change is not
Switching partitions will fail on change tracked changes
Data type changes are not tracked
-
8/11/2019 Change Data Capture and Change Tracking Deep Dive
13/13
WHICH ONE IS RIGHT FOR ME?
Change Data Capture
Works in Enterprise Edition only
Stores every discrete change
Storage intensive
Good for auditing
Requires SQL Server agent
No special serialization required
Change Tracking
Works in all versions of SQL
Returns differences from curr
Storage light
Good for device synchroniza
No job agent required
Operates best with snapshot