CDC on SQL Server

CDC = Change data capture

CDC is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format.

http://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx

Check if cdc enabled:

USE [master]

GO

SELECT [name], database_id, is_cdc_enabled FROM sys.databases

GO

Enable cdc on DB:

USE [DB_NAME]

GO

EXEC sys.sp_cdc_enable_db

GO

after enable cdc:

1. New schema cdc will be created
2. New cdc system tables will be created

-- disable cdc:

EXEC sys.sp_cdc_disable_db 

GO

Check which tables of database have been enabled for cdc:

USE [DB_NAME]

GO

SELECT [name], is_tracked_by_cdc FROM sys.tables 

GO

Activate cdc on a table:

USE [DB_NAME]

GO

EXEC sys.sp_cdc_enable_table 

@source_schema = 'SCHEMA_NAME',

@source_name = 'TABLE_NAME',

@role_name = NULL,

@capture_instance = 'CDC_INSTANCE_NAME',

@supports_net_changes = 0,

@index_name = 'PK_OF_TABLE'

• after enable cdc on a table, a new system table will be created: cdc.CDC_INSTANCE_NAME_CT

For example:

EXEC sys.sp_cdc_enable_table 

@source_schema = 'dbo',

@source_name = 'TableItai',

@role_name = NULL,

@capture_instance = 'dbo_TableItai',

@supports_net_changes = 0,

@index_name = 'PK_TableItai'

-- cdc.dbo_TableItai_CT will be created

Disable cdc on a table:

USE [DB_NAME]

GO

EXECUTE sys.sp_cdc_disable_table

    @source_schema = N'SCHEMA_NAME',

    @source_name = N'TABLE_NAME',

    @capture_instance = N'CDC_INSTANCE_NAME';

GO

https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/

-- enable triggers ON DATABASE
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'DDL_Audit_Trigger')
ENABLE TRIGGER DDL_Audit_Trigger ON DATABASE
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'TRD_AuditObjects')
ENABLE TRIGGER TRD_AuditObjects ON DATABASE
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trg_ProceduresSign')
ENABLE TRIGGER trg_ProceduresSign ON DATABASE

GO

-- disable triggers ON DATABASE
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'DDL_Audit_Trigger')
DISABLE TRIGGER DDL_Audit_Trigger ON DATABASE
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'TRD_AuditObjects')
DISABLE TRIGGER TRD_AuditObjects ON DATABASE
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trg_ProceduresSign')
DISABLE TRIGGER trg_ProceduresSign ON DATABASE

GO