Setting up Amazon RDS for SQL server for CDC (Change data capture)

The following section explains how to set up Amazon RDS for SQL Server for CDC.

To set up Amazon RDS for SQL Server for CDC:

1. Log in as the master user in the DB instance and set up the database for MS-CDC as follows:

   exec msdb.dbo.rds_cdc_enable_db 'db_name'

   where db_name is the name of the source database.

2. To enable MS-CDC, run the following for each of the intended source tables:

   Information note

   If you are running Full Load tasks, this step should only be run after Full Load completes.

   exec sys.sp_cdc_enable_table

   @source_schema = N'schema_name',

   @source_name = N'table_name',

   @role_name = NULL,

   @supports_net_changes = 1

   GO

   where schema_name and table_name are the names of the source schema and the source table respectively.

3. Set the retention period for the changes to be available on the source by changing the @pollinginterval of the 'capture' job to the maximum possible value.

Limitations:

• CDC is supported using the transaction log only (i.e. backup is not supported).
• Events may be lost if they are moved to backup or truncated.