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:
For Amazon RDS for SQL Server, enable change data capture as follows:
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:
If is_tracked_by_cdc returns 0, enable change data capture for each table you want to include in the capture:
The input parameters should be specified as follows:
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 |
@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 |
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:
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