What is CDC in a SQL Server

Using Change Data Capture

Amazon RDS also supports change data capture (CDC) for your DB instances running on Microsoft SQL Server. CDC records changes to data in your tables. It saves metadata about every change that you can access later. For more information about how CDC works, see Change Data Capture in the Microsoft documentation.

Before using CDC for your Amazon RDS DB instances, enable it in the database by running. You must have master user rights to enable CDC on the Amazon RDS DB instance. After CDC has been activated, anyone who is this database can activate or deactivate CDC for tables in this database.

CDC is disabled during a restore. All associated metadata is automatically removed from the database. This applies to snapshot restores, timed restores, and SQL Server Native restores from S3. After you perform either of these types of restores, you can re-enable CDC and redefine tracking tables.

To enable CDC for a DB instance, run the saved procedure.

To disable CDC for a DB instance, run the saved procedure.

Track tables with change data capture

After CDC is enabled for the database, you can start tracking specific tables. You select the tables to track by running sys.sp_cdc_enable_table.

To view the CDC configuration for your tables, run sys.sp_cdc_help_change_data_capture.

For more information about CDC tables, functions, and stored procedures in the SQL Server documentation, see the following:

Change data capture jobs

When you enable CDC, SQL Server creates the CDC jobs. Database owners () can view, create, modify and delete the CDC jobs. However, they are owned by the RDS system account. For this reason, the jobs are not visible in native views, procedures, or in SQL Server Management Studio.

To control the behavior of CDC in a database, use native SQL Server procedures such as sp_cdc_enable_table and sp_cdc_start_job. To change the parameters of CDC jobs like and use sp_cdc_change_job.

For more information on CDC jobs, you can query the following dynamic management views:

  • sys.dm_cdc_errors

  • sys.dm_cdc_log_scan_sessions

  • sysjobs

  • sysjobhistory

Change data capture for Multi-AZ instances

If you are using CDC for a Multi-AZ instance, ensure that the mirror's CDC job configuration matches that on the principal. CDC orders are mapped to the. If the database IDs of the secondary and the principal are different, the jobs will not be linked to the correct database. To avoid errors after failover, RDS discards the jobs on the new principal and recreates them. The newly created jobs use the parameters that the principal recorded before failover.

Although this process is quick, it is still possible that the CDC jobs will run before RDS can correct them. There are three ways to enforce the consistency of the parameters of the primary and secondary replicas:

  • Use the same job parameters for all databases that have CDC enabled.

  • Before changing the CDC job configuration, convert the Multi-AZ instance to Single-AZ.

  • Transfer the parameters manually if you change them on the principal.

To view and define the CDC parameters that are used to rebuild CDC jobs after a failover, use and.

The following example returns the value set for cdc_capture_maxtrans. For all parameters that are set to RDS_DEFAULT, RDS automatically configures the value.

To set up the configuration on the secondary server, run. This procedure sets the parameter values ​​for all databases on the secondary server. These settings are only used after a failover. The following example sets up the for all CDC data entry jobs:

To set the CDC job parameters on the principal, use sys.sp_cdc_change_job instead.