Archive

Archive for the ‘Setup Change Data Capture’ Category

Setup CDC (Change Data Capture)

August 20, 2010 Leave a comment

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.