Microsoft BI

SQL Server 2008: Change Data Capture (CDC)

1. Enable CDC for a database
   1. Enables the current database via the USE statement *select is_cdc_enabled from sys.databases where [name] = 'AdventureWorks' to determine if DB is allready enabled *Also note that when you do this all of the below system objects get created in the selected database
2. Enable CDC for a given table and it's selected columns
   1. Specify the captured table's schema, name, database role, capture instance name (defaults to schema_name), support net changes (bit, set it to 1 if you want both change data table-valued functions created), name of captured table's unique index, captured column list (null/default to all columns), filegroup for the change table (null/defaults to default filegroup)
3. Query Change Data via 1 of 2 built in table-valued functions created during step #2
   1. For all changes (meaning a row is returned for each DML) use cdc.fn_cdc_get_all_changes_<capture_instance>
   2. For the net changes (meaning one row returned for each source row modified among 1 or more DMLs) use cdc.fn_cdc_get_net_changes_<capture_instance>

• cdc.captured_columns
• cdc.change_tables
• cdc.ddl_history
• cdc.index_columns
• cdc.lsn_time_mapping
• cdc.Schema_Name_CT (change tables) *this is just the default naming convention, configurable via the enable table sysproc

• sys.dm_cdc_log_scan_sessions
• sys.dm_repl_traninfo
• sys.dm_cdc_errors

System Stored Procedures:

• sys.sp_cdc_cleanup_change_table
• sys.sp_cdc_disable_db_change_data_capture
• sys.sp_cdc_disable_table_change_data_capture
• sys.sp_cdc_enable_db_change_data_capture
• sys.sp_cdc_enable_table_change_data_capture
• sys.sp_cdc_get_ddl_history
• sys.sp_cdc_get_captured_columns
• sys.sp_cdc_help_change_data_capture

System Functions:

• cdc.fn_cdc_get_all_changes_<capture_instance>
• cdc.fn_cdc_get_net_changes_<capture_instance>
• sys.fn_cdc_decrement_lsn
• sys.fn_cdc_get_column_ordinal ( 'capture_instance' , 'column_name' )
• sys.fn_cdc_get_max_lsn
• sys.fn_cdc_get_min_lsn
• sys.fn_cdc_has_column_changed
• sys.fn_cdc_increment_lsn
• sys.fn_cdc_is_bit_set
• sys.fn_cdc_map_lsn_to_time
• sys.fn_cdc_map_time_to_lsn

How do you leverage CDC in SSIS Packages?

1. Calculate Date Intervals (these will correspond to LSNs later) *also note that in both BOL and my own package we are using fixed intervals, in the real world this will be driven by a table solution which tells the SSIS package when the last successful execution occurs (starting point of next package iteration)
2. Check is any data is available in the selected date/time interval. This is important because the rest of the package will fail if no data is ready. BOL recommends performing Thead.Sleep/WAITFORs here. I am not for demo purposes but its not a bad idea.
3. Build the query via a SSIS variable *BOL states that SSIS packages cannot call the cdc.fn_cdc_getnet|all functions and must use a wrapper. Whether or not we end up being forced to do this, it is a good design practice, below is my custom function that SSIS calls passing in the start/end datetime values to get the actual change data in the data flow step below.
4. Create a data flow task that executes the SSIS variable query (OLEDB source), and then splits the rows into via a conditional split based on the CDC_OPERATION column calculated in the function below.

CREATE function [dbo].[udf_Promotion] (
     @start_time datetime
    ,@end_time datetime
)
returns @Promotion table (
     SpecialOfferID int
    ,Description nvarchar(255)
    ,DiscountPct smallmoney
    ,[Type] nvarchar(50)
    ,Category nvarchar(50)
    ,StartDate datetime
    ,EndDate datetime
    ,MinQty int
    ,MaxQty int
    ,rowguid uniqueidentifier
    ,ModifiedDate datetime
    ,CDC_OPERATION varchar(1)
)
AS
BEGIN
 --declare local variables to hold LSNs
    DECLARE
  @from_lsn binary(10)
  ,@to_lsn binary(10)

 --Map the time interval to a change data capture query range.
    IF (@start_time IS NULL)
 BEGIN
        select @from_lsn = sys.fn_cdc_get_min_lsn('Sales_SpecialOffer')
    END
    ELSE
 BEGIN
  SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @start_time)
    END
   
    IF (@end_time IS NULL)
    BEGIN
        SELECT @to_lsn = sys.fn_cdc_get_max_lsn()
    END
    ELSE
    BEGIN
       SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time)
 END
 
    --if same then exit
    IF (@from_lsn = sys.fn_cdc_increment_lsn(@to_lsn))
    BEGIN   
  RETURN
 END
 
    -- Query for change data
    INSERT INTO @Promotion
    SELECT
        SpecialOfferID,   
        Description,
        DiscountPct,
        [Type],
        Category,
        StartDate,
        EndDate,
        MinQty,
        MaxQty,
        rowguid,
        ModifiedDate,
        CASE __$operation
                WHEN 1 THEN 'D'
                WHEN 2 THEN 'I'
                WHEN 4 THEN 'U'
                ELSE NULL
         END AS CDC_OPERATION
    FROM
        cdc.fn_cdc_get_net_changes_Sales_SpecialOffer(@from_lsn, @to_lsn, 'all')

    RETURN
END

GO

Print | posted on Monday, January 28, 2008 7:48 PM

Copyright © Derek Comingore

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski