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