Changed data from a SQL Server source can be captured only if Change Data Capture (CDC) is enabled at the database, table, and the column levels, in the same order. To perform a changed data transformation from a SQL Server to an Enterprise Database Server, you must enable CDC on all the transformation source tables and the respective columns before loading the schema into the DDW.

Checking if CDC is enabled at the database level

Run the following query to check if CDC is enabled for any database:

SELECT name, is_cdc_enabled FROM sys.databases

Enabling CDC at the database level

Run the following query to enable CDC at database level:

USE [Database_Name]
GO
EXEC sys.sp_cdc_enable_db

Checking if CDC is enabled at the table level

Run the following query to show which tables of database have already been enabled for CDC:

USE [Database_Name]
GO
SELECT name, TYPE, type_desc, is_tracked_by_cdc FROM sys.tables

Enabling CDC at the table level

Run the following script to enable CDC on Table_Name table:

USE [Database_Name]
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'Schema Name', 
@source_name = N'Table_Name',
@role_name = null,
@captured_column_list = N'Column1, Column2' [Optional Parameter],
@capture_instance = N'Specified name' [Optional Parameter],
@supports_net_changes = 1

Run the following script to enable CDC on Partition_Table_Name partition table:

USE [Database_Name]
GO
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo', 
@source_name = 'Partition_Table_Name',
@role_name = null,
@captured_column_list = N'Column1, Column2' [Optional Parameter],
@capture_instance = N'Specified name' [Optional Parameter],
@supports_net_changes = 1,
@allow_partition_switch = 0

Disabling CDC at the database level

Run the following script to disable CDC at the database level:

USE [Database_Name]
GO
EXEC sys.sp_cdc_disable_db

Disabling CDC at the table level

Run the following script to disable CDC at the table level:

EXECUTE sys.sp_cdc_disable_table 
@source_schema = N'schema name', 
@source_name = N'table name',
@capture_instance = N'capture instance name';