Change Data Capture (CDC) feature in sql server 2008. Explain with an example
Change Data Capture is a feature that is used for tracking the changes on a table. The process involves in steps.
Step 1 – Creation of a databaseThe database name is MyDataBase
USE [master]
GO
/*** Object: Database [MyDataBase] ***/
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'MyDataBase')
DROP DATABASE [MyDataBase]
GO
USE [master]
GO
/*** Object: Database [MyDataBase] ***/
CREATE DATABASE [MyDataBase]
GO
Step 2 - Creation of a table in MyDataBase databaseUSE [MyDataBase]
GO
/*** Object: Table [dbo].[MyTable] ***/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]')
AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
USE [MyDataBase]
GO
CREATE TABLE [dbo].[MyTable](
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
CONSTRAINT [MyTable_PK] PRIMARY KEY
GO
Step 3 - Enabling Change Data Capture feature.The Transact SQL command enables the Change Data Capture feature.
After enabling the Change Data Capture, a schema along with objects is created.
USE [MyDataBase]
GO
EXEC sys.sp_cdc_enable_db_change_data_capture
GO
Using the cdc the columns, tables, history of ddl commands, index columns and time mappings are created as follows:
cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping
