Home > Setup Change Data Capture > Setup CDC (Change Data Capture)

Setup CDC (Change Data Capture)

Here we will go over how to setup CDC on an exiting or new database in SQL Server 2008

How to setup Change Data Capture (CDC)

Create a Database or User the exiting Database

1.  Enable the Database for CDC 

Use <Database name>
exec sys.sp_cdc_enable_db

  1. Check the database is enabled for CDC
     select name,is_cdc_enabled from sys.databases where name=’cdc’

3.  Enable Table for Change Data Capture

USE <DatabaseName>;

GO

EXECUTE sys.sp_cdc_enable_table

    @source_schema = N’dbo’

  , @source_name = N’Dep_Master’

  , @role_name = N’cdc_Tables’

  , @capture_instance=’cdc_dbo_DepMaster’;

4.  Get Objects list which are enabled for CDC

 exec sys.sp_cdc_help_change_data_capture

  1. Check minimum LSN for the CDC Table

Use <Database Name>

SELECT sys.fn_cdc_get_min_lsn (‘@Capture_instance’)AS min_lsn;

  1. Check Max LSN

7.  Use <Database Name>

SELECT  sys.fn_cdc_get_max_lsn()

8.  Check all LSN’s

SELECT * FROM cdc.lsn_time_mapping

9.  When u enable cdc for a table couple of things happens at the backgroud

1. creates @capture_instance Object

2. Creates two function as below. This will give u all the details

   fn_cdc_get_all_changes_cdc_dbo_DepMaster

   cdc.fn_cdc_get_net_changes_cdc_dbo_DepMaster

3. Creates two Jobs (Make sure SQL Server Agent Services are running)

cdc.CDC_capture

cdc.CDC_cleanup

10.  Get all the changes happened on the object enabled for CDC
Declare @begin_time datetime,
        @end_time   datetime,
        @from_lsn   binary(10),
        @to_lsn binary(10),

        @max_lsn binary(10);

SET @begin_time=GETDATE()-1;

SET @end_time=GETDATE();

SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn (‘smallest greater than or equal’,@begin_time)

SELECT @to_lsn=sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’,@end_time)

–SELECT @begin_time,@end_time,@from_lsn,@to_lsn

–Get all the changes happened on the object enabled for CDC

SELECT * FROM CDC.fn_cdc_get_all_changes_cdc_dbo_depmaster(@from_lsn,@to_lsn,’all’)

11.  Get all the net changes done on each row of the object enabled for CDC.

Returns one net change row for each source row changed within the specified LSN range. That is, when a source row has multiple changes during the LSN range, a single row that reflects the final content of the row is returned by the function. For example, if a transaction inserts a row in the source table and a subsequent transaction within the LSN range updates one or more columns in that row, the function returns only one row, which includes the updated column values.

Declare @begin_time datetime,
        @end_time   datetime,
        @from_lsn   binary(10),
        @to_lsn binary(10),

        @max_lsn binary(10);

SET @begin_time=GETDATE()-1;

SET @end_time=GETDATE();

SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn (‘smallest greater than or equal’,@begin_time)

SELECT @to_lsn=sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’,@end_time)

–SELECT @begin_time,@end_time,@from_lsn,@to_lsn

–Get all the changes happened on the object enabled for CDC

SELECT * FROM CDC.fn_cdc_get_all_changes_cdc_dbo_depmaster(@from_lsn,@to_lsn,’all’)

  1. To get Date and Time value for transaction sequence number (LSN).

declare @begin_time datetime,@end_time datetime,@from_lsn binary(10),@to_lsn binary(10)

DECLARE @max_lsn binary(10);

SET @begin_time=GETDATE()-1;

SET @end_time=GETDATE();

SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn (‘smallest greater than or equal’,@begin_time)

SELECT @to_lsn=sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’,@end_time)

SELECT @max_lsn = MAX(__$start_lsn)

FROM cdc.fn_cdc_get_all_changes_cdc_dbo_depmaster(@from_lsn, @to_lsn, ‘all’);

SELECT sys.fn_cdc_map_lsn_to_time(@max_lsn);

Will write about how to use SSIS (No thrid party control) to perform ETL from CDCenabled table to Staging or Reporting Database in my next blog.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: