Microsoft SQL Server

This article describes how to enable CDC on your SQL Server database.

This article guides you through the process of enabling CDC on your SQL Server database.

Prerequisites for SQL Server

Upsolver currently supports the following SQL Server set-up:

Amazon RDS for SQL Server
SQL Server 2019 (15x)
SQL Server Standard or Enterprise Edition
SQL Server Agent must be running

Please refer to the Amazon RDS article Using change data capture for more information.

Enable CDC on your database

Ensure you are logged in to SQL Server using an account with sysadmin privileges. From your query window, run the following to enable change data capture on the database:

USE Sales
GO
EXEC sys.sp_cdc_enable_db  
GO

For Amazon RDS for SQL Server, enable change data capture as follows:

// Enable CDC on the Sales database 
EXEC msdb.dbo.rds_cdc_enable_db 'Sales' 
GO

Enable CDC on your tables

After CDC is enabled for a database, any user with db_owner privileges of the database can enable or disable CDC at the table level.

To check if a table has already been enabled for change data capture, run the following:

// Check if the Customer table is enabled for CDC
SELECT  [name], is_tracked_by_cdc 
FROM    sys.tables
WHERE   [name] = 'Customer'

If is_tracked_by_cdc returns 0, enable change data capture for each table you want to include in the capture:

// Enable CDC on the Customer table
USE Sales
GO  
  
EXEC sys.sp_cdc_enable_table  
  @source_schema = N'dbo',  
  @source_name = N'Customer',  
  @role_name = N'cdc_role',  
  @filegroup_name = N'fg_sales_cdc_data',  
  @supports_net_changes = 1  
GO

The input parameters should be specified as follows:

Parameter Description

Parameter	Description
@source_schema	This is the name of the schema to which the table belongs, for example, dbo.
@source_name	The name of the source table you want to enable for change data capture, e.g. Customer.
@role_name	To control access to the changed data, you can optionally specify an existing fixed server or database role. If the database role does not exist, SQL Server creates it. Users must have `SELECT` permissions on all captured columns in the source table and be added to the new role if they are not members of the db_owner or sysadmin roles. Alternatively, if you don't want to use a gating role, set this parameter to NULL, e.g. `@role_name = NULL`.
@filegroup_name	It is best practice to store your change data table separately from the source table. You can optionally specify the name of a pre-existing filegroup to store the CDC data, e.g. fg_sales_cdc_data. If you don't specify an alternative filegroup, CDC data is stored in the default filegroup of the database.
@supports_net_changes	Set this value to 1 if you want a net changes function to be generated for the capture instance. The net changes function will return one change for each distinct row that was changed in the specified interval in the call. For more information, please refer to cdc.fn_cdc_get_net_changes_<capture_instance>. The net changes function requires the source table to include a primary key or unique index. If using the latter, you must include the `@index_name` parameter to specify the name of the unique index.

@source_schema

This is the name of the schema to which the table belongs, for example, dbo.

@source_name

The name of the source table you want to enable for change data capture, e.g. Customer.

@role_name

To control access to the changed data, you can optionally specify an existing fixed server or database role. If the database role does not exist, SQL Server creates it. Users must have SELECT permissions on all captured columns in the source table and be added to the new role if they are not members of the db_owner or sysadmin roles. Alternatively, if you don't want to use a gating role, set this parameter to NULL, e.g. @role_name = NULL.

@filegroup_name

It is best practice to store your change data table separately from the source table. You can optionally specify the name of a pre-existing filegroup to store the CDC data, e.g. fg_sales_cdc_data. If you don't specify an alternative filegroup, CDC data is stored in the default filegroup of the database.

@supports_net_changes

Set this value to 1 if you want a net changes function to be generated for the capture instance. The net changes function will return one change for each distinct row that was changed in the specified interval in the call. For more information, please refer to cdc.fn_cdc_get_net_changes_<capture_instance>. The net changes function requires the source table to include a primary key or unique index. If using the latter, you must include the @index_name parameter to specify the name of the unique index.

Specifying capture columns

By default, when you enable CDC on a table, all columns are identified as captured columns. If you don't need to track all columns or want to exclude specific columns for privacy reasons for example, you can use the @captured_column_list parameter:

// Enable CDC on the Customer table and limit the captured columns
USE Sales
GO  
  
EXEC sys.sp_cdc_enable_table  
  @source_schema = N'dbo',  
  @source_name = N'Customer',  
  @role_name = N'cdc_role',  
  @captured_column_list = 'CustomerID, Title, FirstName, LastName, CompanyName'
  @filegroup_name = N'fg_sales_cdc_data',    
  @supports_net_changes = 1  
GO

If you have set @support_net_changes to 1 to switch on net change tracking, you must include either the primary key column or the columns defined in the unique index in the @captured_column_list parameter.

Learn More

For more detailed information on CDC, please refer to the Enable and disable change data capture guide from Microsoft.

Last updated 3 months ago