Change Data Capture in SQl Server

Change Data Capture (CDC): This is a new feature available in SQL Server 2008 to track INSERTED, UPDATED or DELETED records.

Steps to enable CDC:

1.       Check CDC Status in your databases.

Check the status of “is_CDC_enabled” column by running below query:

SELECT [Name], database_ID, is_cdc_enabled FROM SYS.DATABASES

WHERE [name] = 'BIPractice'

2.       To enable change data capture in your database execute the below query:

USE BIPractice -–[dbname]

GO

EXEC sys.sp_cdc_enable_db

GO 

Verify CDC Method 1: And then run the below query to verify CDC configuration:

Verify CDC Method 2: A new Schema CDC will be created in Schemas folder in your database.

Verify CDC Method 3: The following system tables will be created in the database:

Ø  cdc.captured_columns: This table returns result for list of captured column.

Ø  cdc.change_tables: This table returns list of all the tables which are enabled for capture.

Ø  cdc.ddl_history: This table contains history of all the DDL changes since capture data enabled.

Ø  cdc.index_columns: This table contains indexes associated with change table.

Ø  cdc.lsn_time_mapping: This table maps LSN number (for which we will learn later) and time.

3.       Run the below query to enable CDC capture against the table which you want to track, e.g., dbo.Sales.

 exec sys.sp_cdc_enable_table

    @source_schema = 'dbo',

    @source_name = 'sales' ,

    @role_name = 'db_reader',

 @supports_net_changes = 1

On successful execution, the following messages are displayed:

Job 'cdc.BIPractice_capture' started successfully.

Job 'cdc.BIPractice_cleanup' started successfully.

               

Ø  @source_schema is the schema name of the table that you want to enable for CDC

Ø  @source_name is the table name that you want to enable for CDC

Ø  @role_name is a database role which will be used to determine whether a user can access the CDC data; the role will be created if it doesn't exist.  You can add users to this role as required; you only need to add users that aren't already members of the db_owner fixed database role.

Ø  @supports_net_changes determines whether you can summarize multiple changes into a single change record; set to 1 to allow, 0 otherwise.

4.       In order to view the tables for which CDC is enabled run the below query:

 SELECT name, type, type_desc, is_tracked_by_cdc from sys.tables

    WHERE is_tracked_by_cdc = 1

Example:

1.       Create Table Sales

CREATE TABLE [dbo].[Sales](

       [RowKey] [int] IDENTITY(1,1) NOT NULL,

       [Firstname] [varchar](50) NOT NULL,

       [ProductCategory] [varchar](50) NOT NULL,

       [Product] [varchar](50) NULL,

       [CreatedDate] [date] NULL,

       [Country] [varchar](50) NULL,

       [State] [varchar](50) NULL,

       [City] [varchar](50) NULL,

       [Sales] [float] NULL,

 CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED

(

       [RowKey] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

2.       Enable CDC for the Table Sales

3.        Inert record

INSERT INTO [BIPractice].[dbo].[Sales]

([Firstname],[ProductCategory],[Product],[CreatedDate],[Country],[State],[City],[Sales])

VALUES('ABC','ABC','ABC','1/1/2010','India','Taminadu','Chennai',1234)

Run the below Query:

DECLARE @begin_lsn BINARY(10), @end_lsn BINARY(10)

SELECT @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_sales')

SELECT @end_lsn = sys.fn_cdc_get_max_lsn()

PRINT @begin_lsn

PRINT @end_lsn

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_sales(@begin_lsn, @end_lsn, 'all');

The __$operation column stands for

1 = delete

 2 = insert, 

3 = update (values before update),

4 = update (values after update). 

(Try executing update edit statements in the table)


--==============================================================================
--DISABLE CHANGE DATA CAPTURE IN TABLE
--==============================================================================

USE [CDC]
GO

EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name   = N'sales',
@capture_instance = N'dbo_sales'
GO

--================================================================================

--==============================================================================
--DISABLE CHANGE DATA CAPTURE IN DATABASE
--==============================================================================

USE [CDC]
GO
EXEC sys.sp_cdc_disable_db
GO 
--******************************************************************************