SQL Server 2008 Change Tracking (CT) and Change Data Capture (CDC)

Here are some notes on "SQL Server 2008 Change Tracking (CT) and Change Data Capture (CDC)" I took while attending an advanced class on SQL Server taught by Paul Nielsen (from https://www.sqlserverbible.com).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

Auditing and Monitoring

  • Before SQL Server 2000: Profiler/Trace, after triggers, sysmon/perfmon, error log
  • SQL Server 2000 and later: C2Audit, Security Login to Logs
  • SQL Server 2005 and later: Triggers (instead of, logon, DDL), CommonCriteria, DMVs, BBTrace, Event Notification
  • SQL Server 2008 and later: Change Tracking, CDC, Extended Events, SQLAudit, MDW/Collector, PBM

Change Tracking

  • Change Tracking is lightweight, easy to implement, good for synching/ETL
  • Change Tracking is synchronous, happens as part of the transaction
  • Change Tracking not good for auditing, limited context info
  • Change Tracking main use cases ETL update / custom synch with offline app
  • Change Tracking set up with ALTER DATABASE / ALTER TABLE
  • Query with functions
  • Test for column changes, store context info from app code
  • Use SNAPSHOT ISOLATION to avoid version changes during the operation

Change Tracking – Demo - Database

  • ALTER DATABASE name SET CHANGE_TRACKING=ON
  • (CHANGE_RETENTION=24 HOURS, AUTO_CLEANUP=ON)
  • Need to pull the data every 24 hours, so you don’t lose any changes
  • You can change settings and disable it
  • Check with Select * from sys.change_tracking_databases
  • Can also be configured/changed with SSMS
  • See https://msdn.microsoft.com/en-us/library/bb964713.aspx

Change Tracking – Demo – Table

  • ALTER TABLE name ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED=ON)
  • Primary key required on the table
  • For all tables: EXEC sp_MSforeachtable ‘ALTER TABLE ? ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED=ON)’
  • Check with sys.internal_tables, sys.change_tracking_tables – can find the actual table used for tracking
  • Current version via change_tracking_current_version() – database wide, starts with 0
  • Can also be configured/changed with SSMS
  • See https://msdn.microsoft.com/en-us/library/bb933949.aspx

Change Tracking – Demo – Synchronization

  • Using change tracking to update synch changes from offline application
  • Set it up, insert a few rows in a table
  • Select * from CHANGETABLE(CHANGES table, version)
  • Select change_tracking_min_valid_version
  • Created SP to update main table with a MERGE statement, keep track with last version
  • Made changes, synched, checked if it worked with a FULL OUTER JOIN
  • Used CHANGE_TRACKING_IS_COLUMN_IN_MASK, sys_change_columns to find out which columns changed
  • Used CHANGETABLE(VERSION table…
  • Set security context WITH CHANGE_TRACKING_CONTEXT (string) DML
  • Query later in ApplicationContext column in change table

Change Data Capture – CDC

  • Efficient for ETL, Enterprise Edition Only, Uses Transaction Log
  • Asynchronous, incremental data changes, requires SQL Server agent
  • Job that pulls off the transaction log and writes to specific tables
  • There’s still a performance hit, but could be used for OLTP
  • Will hold the log until it is consumed by the async job.
  • White Paper at https://msdn.microsoft.com/en-us/library/dd266396.aspx

CDC – Setup for DB

CDC – Setup for table

  • Enable for table: sp_cdc_enable_table
  • Disabling for table: sp_cdc_disabe_table
  • Creates two jobs – Capture and cleanup
  • Creates a system table – cdc.schema_table_ct
  • Same columns as base table, plus _$start_lsn, _$end_lsn, _$operation, _$seqval, _$updatemask

CDC functions

  • Sys.fn_cdc_map_time_to_lsn(relational operator, time)
  • Sys.fn_cdc_map_lsn_to_time
  • Sys.fn_cdc_increment_lsn
  • Sys.fn_cdc_decrement_lsn
  • Sys.fn_cdc_get_max_lsn
  • Sys.fn_cdc_get_min_lsn
  • fn_cdc_get_all_changes_schema_table
  • fn_cdc_get_net_change_schema_table
  • sys.fn_cdc_is_bit_set
  • Sys.fn_cdc_get_column_ordinal
  • See https://msdn.microsoft.com/en-us/library/cc645858.aspx

Comparing CDC and CT

Nielsen’s own: AutoAudit

Comments

  • Anonymous
    January 01, 2003
    PingBack from http://servercoach.com/?p=1230
  • Anonymous
    January 01, 2003
    I took some advanced SQL Server 2008 classes recently and shared my notes in a series of blog posts.
  • Last updated on