Help Center> Relational Database Service> User Guide> Working with RDS for SQL Server> Usage of Stored Procedures> Capturing Change Data
View PDF

Capturing Change Data

Scenarios

You can use a stored procedure to enable or disable the change data capture function for a specified database. Change data capture can record the insertion, update, and deletion activities of an enabled table, and provide detailed change information using an easy-to-use relational format.

Only RDS for SQL Server Enterprise Editions support the change data capture function.

For more information about change data capture, see the official documents.

Prerequisites

  • An RDS Microsoft SQL Server DB instance has been connected. For details about how to connect to a DB instance, see Connecting to a DB Instance Through a Public Network.
  • The stored procedure must be executed by a user who has the [CREATE ANY DATABASE] permission. If a user who does not have this permission attempts to execute the stored procedure, the system displays the following information: 
    Database restores can only be performed by database logins with [CREATE ANY DATABASE] permissions.

Constraints

  • The change data capture function cannot be enabled or disabled for system databases. If you attempt to enable or disable change data capture for a system database, the system displays the following information: 
    CDC can not open on system database and [rdsadmin].
  • The change data capture operation can only be 1 or 0. If other operations are performed, the system displays the following information: 
    @dbAction just support 1:open 0:close

Procedure

Run the following command to enable or disable change data capture:

EXEC msdb.dbo.rds_cdc_db '@DBName', @dbAction;

  • @DBName: specifies the database on which to be operated.
  • @dbAction: specifies the operation. The value 1 indicates that enable change data capture. The value 0 indicates that disable change data capture.

For example, to enable the change data capture function for testDB_1, run the following command:

EXEC msdb.dbo.rds_cdc_db 'testDB_1', 1;