1. Oracle GoldenGate Microservices Documentation
  2. Prepare
  3. Prepare Databases
  4. SQL Server
  5. Transaction Log Settings and Requirements
  6. Preparing the Source Database for Oracle GoldenGate Capture
  7. Enabling CDC Supplemental Logging

Enabling CDC Supplemental Logging

With the CDC Extract, the method of capturing change data is via SQL Server Change Data Capture tables, so it is imperative that you follow the procedures and requirements below, so that change data is correctly enabled, maintained, and captured by Extract.

You will enable supplemental logging with the ADD TRANDATA command so that Extract can capture the information that is required to reconstruct transactions.

ADD TRANDATA must be issued for all tables that are to be captured by Oracle GoldenGate, and to do so requires that a valid schema be used in order to create the necessary Oracle GoldenGate tables and stored procedures.

Enabling supplemental logging for a CDC Extract does the following:

  • Enables SQL Server Change Data Capture at the database level, if it’s not already enabled.

  • Creates a Change Data Capture staging table for each base table enabled with supplemental logging by running EXECUTE sys.sp_cdc_enable_table, and creates a trigger for each CDC table. The CDC table exists as part of the system tables within the database and has a naming convention like, cdc.OracleGG_basetableobjectid_CT.

  • Creates a tracking table of naming convention ggschema.OracleGGTranTables. This table is used to store transaction indicators for the CDC tables and is populated when the trigger for a CDC table is fired. The table will be owned by the schema listed in the GLOBALS file’s, GGSCHEMA parameter.

  • Creates a unique fetch stored procedure for each CDC table, as well as several other stored procedures that are required for Extract to function. These stored procedures will be owned by the schema listed in the GLOBALS file’s, GGSCHEMA parameter.

  • Also, as part of enabling CDC for tables, SQL Server creates two jobs per database:

    cdc.dbname_capture

    cdc.dbname_cleanup

  • The CDC Capture job is the job that reads the SQL Server transaction log and populates the data into the CDC tables, and it is from those CDC tables that the Extract will capture the transactions. So it is of extreme importance that the CDC capture job be running at all times. This too requires that SQL Server Agent be set to run at all times and enabled to run automatically when SQL Server starts.

    Note:

    If SQL Server Transactional Replication is also enabled for the database, the CDC Capture job will not exist and instead, only the SQL Server Log Reader Agent job will exist.

  • The CDC Capture job can be tuned for better throughput and tuning information can be found in CDC Capture Method Operational Considerations.

  • The CDC Cleanup job that is created by Microsoft does not have any dependencies on whether the Oracle GoldenGate Extract has captured data in the CDC tables or not. Therefore, extra steps need to be followed in order to disable or delete the CDC cleanup job immediately after TRANDATA is enabled, and to enable Oracle GoldenGate's own CDC cleanup job or Purge Change Data task. See Retaining the CDC Table History Data for more information.

To enable supplemental logging using the command line interface, use the following high-level steps:

  1. Review the Prepare Database Users and Privileges topic in order to determine required privileges and steps to enable the database for Change Data Capture, if it is not already set. Elevated permissions may be needed for GoldenGate if the database is not enabled for CDC but can be negated by having an admin manually enable the database for Change Data Capture.

    • For Google Cloud SQL for SQL Server , the database has to manually be enabled for Change Data Capture by a service admin user and executing the following command:

      EXEC msdb.dbo.gcloudsql_cdc_enable_db 'source_database';

    • For SQL Server and Azure SQL Managed Instance, adding TRANDATA will attempt to set the database for Change Data Capture if the user has sysadmin privileges, otherwise a database administrator can manually enable the database for CDC prior to adding TRANDATA, by executing the following command against the source database:

      EXEC sys.sp_cdc_enable_db;

    • For Amazon RDS for SQL Server, adding TRANDATA will also attempt to set the database for Change Data Capture if the user has been granted the permission, otherwise a database administrator with master credentials can manually enable the database for CDC prior to adding TRANDATA, by executing the following command against the source database:

      EXEC msdb.dbo.rds_cdc_enable_db ‘source_database’

  2. In the source Oracle GoldenGate installation, ensure that the GLOBALS file has the parameter GGSCHEMA schemaname and that the schema name used has been created (CREATE SCHEMA schemaname) in the source database. This schema will be used by all subsequent Oracle GoldenGate components created in the database, therefore it is recommended to create a unique schema that is solely used by Oracle GoldenGate, such as ’ggschema’ and to not use the SQL Server schemas dbo or cdc.

  3. On the source Oracle GoldenGate system, open the command line interface (Admin Client).

  4. Connect to the database with the database login credentials.

  5. Issue the following command for each table that is to be captured by an Extract. You can use a wildcard to specify multiple table names.

    ADD TRANDATA owner.table

    ADD TRANDATA owner.*

    Optionally, you can designate the filegroup in which the SQL Server Change Data Capture staging tables will be placed, by using the FILEGROUP option with an existing filegroup name.

    ADD TRANDATA owner.table FILEGROUP cdctablesSee ADD TRANDATA

See ADD TRANDATA for more details.

A sample tutorial to setup GoldenGate CDC Capture is available:

Using Oracle GoldenGate for SQL Server CDC Capture ReplicationUsing Oracle GoldenGate 19.1 for SQL Server CDC Capture Replication.