SQL Server

This section lists details about configuring Oracle GoldenGate for SQL Server.

Topics:

Parent topic: Configure

Prepare Database Users and Privileges

Learn about required database users, privileges, and permissions for Oracle GoldenGate for SQL Server, including supported SQL Server cloud databases.

Topics:

Parent topic: SQL Server

Oracle GoldenGate for SQL Server

Oracle GoldenGate processes require a database user in order to capture from and apply data to a SQL Server database and it is recommended to create a dedicated database user to be used exclusively by GoldenGate processes.

Oracle GoldenGate for SQL Server supports SQL Server authentication for all of its certified platforms and Windows authentication for Classic Architecture only when Oracle GoldenGate is installed on a Windows server.

To use Windows authentication for Oracle GoldenGate Classic Architecture, the Extract and Replicat processes inherit the login credentials of the Manager process. By default, the Manager process runs interactively as the user logged on to the Windows server or optionally can be added as a Windows Service with a default service name of GGSMGR. Whichever method that the Manager process is using, the user that it is running as needs to have the required SQL Server privileges listed above.
To use SQL Server authentication, create a dedicated SQL Server login for Extract and Replicat and assign the privileges listed below.

SQL Server and Azure SQL Managed Instance

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to capture from and apply to a SQL Server or Azure SQL Managed Instance database.

Create a dedicated login for Oracle GoldenGate for SQL Server or Azure SQL Managed Instance.
Add the login as a user to the msdb database and to the source or target database.
Create a schema in the source or target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.
Enable the following privileges and permissions for the Oracle GoldenGate user based on whether the user is for an Extract, or for a Replicat.

Table 4-3 Privileges and Permissions for Oracle GoldenGate User

Privilege	Extract	Replicat	Syntax
msdb Database Roles and Privileges
`SQLAgentReaderRole`	Yes	No	`ALTER ROLE SQLAgentReaderRole ADD MEMBER gguser;`
`SQLAgentUserRole`	Inherited	Yes	`ALTER ROLE SQLAgentUserRole ADD MEMBER gguser;`
`SELECT ON sysjobactivity`	Yes	No	Required for Classic Architecture only. `GRANT SELECT ON msdb.dbo.sysjobactivity TO gguser;`
`SELECT ON sysjobs`	Yes	No	Required for Classic Architecture only. `GRANT SELECT ON msdb.dbo.sysjobs TO gguser;`
User Database Roles and Privileges
`SYSADMIN`	Yes	No	Required for a one time change to enable database level Change Data Capture (CDC) if not already enabled and can be revoked once `TRANDATA` has been enabled. `ALTER SERVER ROLE sysadmin ADD MEMBER gguser;` Database Administrators with sysadmin credentials can manually enable the database for CDC using the following, which would negate the need for the Extract user to have this privilege: `EXEC msdb.sys.sp_cdc_enable_db ‘source_database’`
`DBOWNER`	Yes	Yes	`ALTER ROLE db_owner ADD MEMBER gguser;`

Parent topic: Prepare Database Users and Privileges

Amazon RDS for SQL Server

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to capture from and apply to an Amazon RDS for SQL Server database:

Create a dedicated login for Oracle GoldenGate for Amazon RDS for SQL Server.
Add the login as a user to the msdb database and to the source or target database.
Create a schema in the source or target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.
Enable the following privileges and permissions for the Oracle GoldenGate user based on whether the user is for an Extract, or for a Replicat.

Table 4-4 Privileges and Permissions for Oracle GoldenGate User

Privilege	Extract	Replicat	Syntax
msdb Database Roles and Privileges
`EXECUTE ON rds_cdc_enable_db`	Yes	No	`GRANT EXECUTE ON msdb.dbo.rds_cdc_enable_db TO gguser;` Database administrators with master credentials can manually enable the database for Change Data Capture using the following command, which would negate the need for the Extract user to have this permission: `EXEC msdb.dbo.rds_cdc_enable_db ‘source_database’`
`SQLAgentOperatorRole`	Yes	No	`ALTER ROLE SQLAgentOperatorRole ADD MEMBER gguser;`
`SQLAgentUserRole`	Inherited	Yes	`ALTER ROLE SQLAgentUserRole ADD MEMBER gguser;`
`SELECT ON sysjobactivity`	Yes	No	Required for Classic Architecture only. `GRANT SELECT ON msdb.dbo.sysjobactivity TO gguser;`
`SELECT ON sysjobs`	Yes	No	Required for Classic Architecture only. `GRANT SELECT ON msdb.dbo.sysjobs TO gguser;`
User Database Roles and Privileges
`DBOWNER`	Yes	Yes	`ALTER ROLE db_owner ADD MEMBER gguser;`

Parent topic: Prepare Database Users and Privileges

Azure SQL Database

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to apply to an Azure SQL Database:

Create a dedicated login for Oracle GoldenGate for Azure SQL Database.
Add the login as a user to the target database.
Create a schema in the target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.
Enable the following privileges and permissions for the Oracle GoldenGate user.

Table 4-5 Privileges and Permissions for Oracle GoldenGate User

Privilege	Extract	Replicat	Syntax
User Database Roles and Privileges
`DBOWNER`	NA	Yes	`ALTER ROLE db_owner ADD MEMBER gguser;`

Parent topic: Prepare Database Users and Privileges

Google Cloud SQL for SQL Server

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to capture from and apply to a Google Cloud SQL for SQL Server database:

Create a dedicated login for Oracle GoldenGate Google Cloud SQL for SQL Server. The user must be created from within the Users section of the Google Cloud dashboard for the database instance.
Add the user to the source or target database.
Create a schema in the source or target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.
If the database is to be used as a source for an Extract, manually enable the database for Change Data Capture (CDC):

EXEC msdb.dbo.gcloudsql_cdc_enable_db 'source_database';
Enable the following privileges and permissions for the Oracle GoldenGate user based on whether the user is for an Extract, or for a Replicat.

Table 4-6 Privileges and Permissions for Oracle GoldenGate User

Privilege	Extract	Replicat	Syntax
User Database Roles and Privileges
`DBOWNER`	Yes	Yes	`ALTER ROLE db_owner ADD MEMBER gguser;`

Parent topic: Prepare Database Users and Privileges

Prepare Database Connection, System, and Parameter Settings

Learn about configuring database connection, system, and parameter settings for Oracle GoldenGate for SQL Server.

Topics:

Parent topic: SQL Server

Configuring a Database Connection

Learn about configuring a database connection for SQL Server.

Topics:

Parent topic: Prepare Database Connection, System, and Parameter Settings

Extract and Replicat Database Connectivity

Extract and Replicat connect to a SQL Server database using a system ODBC DSN (Data Source Name) and use ODBC for its metadata queries and transactional data processing.

Parent topic: Configuring a Database Connection

Creating a Database Connection on Linux

Before creating a database connection for Oracle GoldenGate processes running on Linux, install the latest version of Microsoft ODBC driver for SQL Server (Linux).

Select the following link for download and installation steps:

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15

For the installation, choose the steps listed under Red Hat Enterprise Linux and Oracle.

After the ODBC software is installed, follow the example below to create an ODBC DSN for Linux:

Create a template file for your data source(s):

vi odbc_template_file.ini
Describe the data source in the template file. Multiple unique DSN entries can be listed in the template file, if needed.
In the following example, mydsn_2019_source is the DSN name, which will be used with DBLOGIN and SOURCEDB or TARGETDB to connect to the Extract or Replicat to the database.
```
mydsn_2019_source]            
Driver = ODBC Driver 18 for SQL Server            
Server = myserver,1433            
Database = source_database
TrustServerCertificate=YES
```
Install the data source using the following command.

odbcinst -i -s -f odbc_template_file.ini

This command adds the DSN to the system odbc.ini file. For more information, select the following link:

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/connection-string-keywords-and-data-source-names-dsns?view=sql-server-2017

Parent topic: Configuring a Database Connection

Creating a Database Connection on Windows

Before creating a database connection for Oracle GoldenGate processes running on Windows, install the latest version of Microsoft ODBC Driver for SQL Server.

Follow these steps to create a system DSN on the Windows server where Oracle GoldenGate is installed.

To create a SQL Server DSN,

Open the ODBC Data Sources (64-bit) application.
In the ODBC Data Source Administrator dialog box, select the System DSN tab, and then click Add.
Under Create New Data Source, select the ODBC Driver {version} for SQL Server and then click Finish. The Create a New Data Source to SQL Server wizard appears.
Enter the following details, and click Next:
- Name: Can be of your choosing. In a Windows cluster, use one name across all nodes in the cluster.
- Description: (Optional) Type a description of this data source.
- Server: Type the SQL Server connection string or server\instance name. For Always On connections, use the listener\instance name of the Always On Availability Group.
For login authentication, select one of the following options, and then click Next:
1. With Integrated Windows Authentication
2. With SQL Server authentication using a login ID and password entered by the user
Select Change the default database to, and then select the source or target database from the list. Enable the Use ANSI settings. Click Next.
Leave the next page set to the defaults. Click Finish.
Click Test Data Source to test the connection.
If the test is successful, close the confirmation box and the Create a New Data Source box.
Repeat this procedure for each SQL Server source and target database.

Parent topic: Configuring a Database Connection

Connecting to the Listener of a SQL Server Always On Configuration

Extract and Replicat can connect to the listener of an Always On configuration or directly to the current primary replica of the group, depending on the DSN connection used.

The advantage of creating the connection to the listener is that Extract or Replicat can reconnect to the new primary replica upon failover without having to reconfigure the DSN to the new primary.

An Extract can also be configured to route its read-only queries to an available readable, synchronous mode secondary replica. By default, if Extract connects to a listener, all processing will be done against the primary replica, but if an Extract is configured with the TRANLOGOPTIONS ALWAYSONREADONLYROUTING parameter, its read-only queries are routed by the listener to an available readable secondary replica.

See TRANLOGOPTIONS and Requirements Summary for Capture and Delivery of Databases in an Always On Availability Group for more information.

If creating the DSN to connect to a Listener of an Always On configuration, enable the Multi-subnet failover option when creating the DSN. For Linux DSN connections, use the MultiSubnetFailover=Yes option in the DSN entry.

Parent topic: Configuring a Database Connection

Configuring a Database

Learn about configuring a database for SQL Server.

Topics:

Parent topic: Prepare Database Connection, System, and Parameter Settings

SQL Server Supported Versions

Certified versions of SQL Server can be found on the published certification matrix available for each release of Oracle GoldenGate, which is available at the following link:

https://www.oracle.com/middleware/technologies/fusion-certification.html

Oracle GoldenGate Extract supports Enterprise Edition and some versions of SQL Server Standard Edition. Review the Exceptions and Additonal Information column of the certification matrix to see the details of which Standard Edition versions of SQL Server are supported for Extract.

Oracle GoldenGate Delivery supports both SQL Server Enterprise and Standard editions.

Oracle GoldenGate supports remote capture and delivery for Azure SQL Database Managed Instance and remote delivery for Azure SQL Database.

Oracle GoldenGate supports remote capture and delivery for Amazon RDS for SQL Server.

Parent topic: Configuring a Database

Preparing Tables for Processing

The table attributes in the following sections must be addressed in your Oracle GoldenGate environment.

Topics:

Parent topic: Configuring a Database

Disabling Triggers and Cascade Constraints on the Target

In an environment where SQL Server is the target, consider triggers and cascade constraints that may repeat an operation that occurred on the source. For example, if the source has an insert trigger on TableA that inserts a record into TableB, and Oracle GoldenGate is configured to capture and deliver both TableA and TableB, the insert trigger on the target table, TableA, must be disabled. Otherwise, Replicat inserts into TableA, and the trigger fires and insert into TableB. Replicat will then try to insert into TableB, and then terminate abnormally.

When a trigger or cascade constraint repeats an operation that occurred on the source, you do not have to disable the trigger or constraint when the following conditions are both true:

You use the DBOPTIONS USEREPLICATIONUSER parameter in Replicat.
You use OLE DB connection for Replicat. The use of the OLE DB connection is the default configuration. Note that the trigger, constraint, or IDENTITY property must have NOT FOR REPLICATION enabled.

In the following scenario, disable the triggers and constraints on the target:

Uni-directional replication where all tables on the source are replicated.

In the following scenarios, enable the triggers and constraints on the target:

Uni-directional replication where tables affected by a trigger or cascade operation are not replicated, and the only application that loads these tables is using a trigger or cascade operation.
Uni-directional or -bi-directional replication where all tables on the source are replicated. In this scenario, set the target table cascade constraints and triggers to enable NOT FOR REPLICATION, and use the DBOPTIONS USEREPLICATIONUSER parameter in Replicat.

Parent topic: Preparing Tables for Processing

Replicat Consideration for Target Identity Columns, Triggers, and Constraints

When replicating data to a target SQL Server database that has identity columns, triggers, and cascade and check constraints, consider the following:

For columns that have an identity column, Replicat sets the IDENTITY_INSERT ON for the table, which may reduce delivery performance.
For tables that have triggers or cascade constraints, execution of the trigger or cascade operation may result in a Replicat error if the Replicat is configured to deliver the same data that a trigger will insert or cascade constraint will update or delete.

For example, TableA on the source has a trigger that inserts a record into TableB. The Extract is configured to capture records for both TableA and TableB. On the target, the Replicat will first insert a record for TableA, then the trigger for TableA fires and inserts into TableB, followed by the Replicat attempting to insert the same record into TableB, which will result in a Replicat error.
Check any foreign key constraints are also enforced, which may reduce delivery performance.

To overcome these situations, there are several options that can be implemented based on the replication use case.

For unidirectional implementations where a Replicat is the only process writing data to the target tables, consider the following options for Identity columns, triggers and constraints on the target tables.
1. Disable or drop the Identity property, triggers and constraints on the target tables.
2. Modify the identity property, triggers and constraints and set the NOT FOR REPLICATION option on for each and ensure that the Microsoft ODBC driver is at least version 17.8.1.
For multi-directional implementations where both a Replicat and application write data to the target tables, and triggers and constraints are enabled, modify the Identity property, triggers and constraints and set the NOT FOR REPLICATION option on for each and ensure that the Microsoft ODBC driver is at least version 17.8.1.

Additionally, to use IDENTITY columns in a multi-directional replication configuration, define the IDENTITY columns to have an increment value equal to the number of servers in the configuration, with a different seed value for each one.

For example, a three-database configuration would be as follows:

Database1 set the seed value at 0 with an increment of 3.

Database2 set the seed value at 1 with an increment of 3.

Database3 set the seed value at 2 with an increment of 3.

Parent topic: Preparing Tables for Processing

Setting the NOT FOR REPLICATION flag for Target Identity Columns, Triggers, and Constraints

Set the NOT FOR REPLICATION flag on the following objects.
- Foreign key constraints
- Check constraints
- IDENTITY columns
- Triggers (requires textual changes to the definition. See the SQL Server documentation for more information.)
For active-passive configurations, set it only on the passive database. For active-active configurations, set it on both databases.
Partition IDENTITY values for bidirectional configurations.
In the Replicat MAP statements, map the source tables to appropriate targets, and map the child tables that the source tables reference with triggers or foreign-key cascade constraints. Triggered and cascaded child operations are replicated by Oracle GoldenGate, so the referenced tables must be mapped to appropriate targets to preserve data integrity. Make sure to include the same parent and child source tables in the Extract TABLE parameters.

Note:

If referenced tables are omitted from the MAP statements, no errors alert you to integrity violations, such as if a row gets inserted into a table that contains a foreign key to a non-replicated table.

Parent topic: Preparing Tables for Processing

Ensuring Row Uniqueness in Source and Target Table

Oracle GoldenGate requires some form of unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes.

Unless a KEYCOLS clause is used in the TABLE or MAP statement, Oracle GoldenGate selects a row identifier to use in the following order of priority:

Primary key
First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.
If none of the preceding key types exist (even though there might be other types of keys defined on the table) Oracle GoldenGate constructs a pseudo key of all columns that the database allows to be used in a unique key, excluding those that are not supported by Oracle GoldenGate in a key or those that are excluded from the Oracle GoldenGate configuration.

Note:

If there are other, non-usable keys on a table or if there are no keys at all on the table, Oracle GoldenGate logs an appropriate message to the report file. Constructing a key from all of the columns impedes the performance of Oracle GoldenGate on the source system. On the target, this key causes Replicat to use a larger, less efficient WHERE clause.
If a table does not have an appropriate key, or if you prefer that the existing key(s) are not used, you can define a substitute key, if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. See TABLE | MAP in Reference for Oracle GoldenGate.

Using KEYCOLS to Specify a Custom Key

Parent topic: Preparing Tables for Processing

Using KEYCOLS to Specify a Custom Key

If a table does not have an applicable row identifier, or if you prefer that identifiers are not used, you can define a substitute key, providing that the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter. The specified key overrides any existing primary or unique key that Oracle GoldenGate finds.

Parent topic: Ensuring Row Uniqueness in Source and Target Table

Improving IDENTITY Replication with Array Processing

Because only one table per session can have IDENTITY_INSERT set to ON, Replicat must continuously toggle IDENTITY_INSERT when it applies IDENTITY data to multiple tables in a session. To improve the performance of Replicat in this situation, use the BATCHSQL parameter. BATCHSQL causes Replicat to use array processing instead of applying SQL statements one at a time.

Parent topic: Preparing Tables for Processing

Transaction Log Settings and Requirements

Know more about transaction log settings and requirements for Oracle GoldenGate for SQL Server.

Topics:

Parent topic: SQL Server

Preparing the Source Database for Oracle GoldenGate Capture

Learn how to enable supplemental logging in the source database and tables that are to be used for capture by the Extract for SQL Server and how to purge older Change Data Capture staging data.

Topics:

Parent topic: Transaction Log Settings and Requirements

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:

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’
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.
On the source Oracle GoldenGate system, open the command line interface (Admin Client).
Connect to the database with the database login credentials.
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.

Parent topic: Preparing the Source Database for Oracle GoldenGate Capture

Purging CDC Staging Data

When enabling supplemental logging, data that is required by Extract to reconstruct transactions are stored in a series of SQL Server CDC system tables, as well Oracle GoldenGate objects that are used to track operation ordering within a transaction. These tables require routine purging in order to reduce data storage within the database. As part of enabling supplemental logging using TRANDATA, SQL Server creates its own Change Data Capture Cleanup job, however this job is unaware that an Extract may still require data from these CDC system tables and can remove that data before the Extract has a chance to capture it.

If data that Extract needs during processing has been deleted from the CDC system tables, then one of the following corrective actions might be required:

Alter Extract to capture from a later point in time for which CDC data is available (and accept possible data loss on the target).
Resynchronize the source and target tables, and then start the Oracle GoldenGate environment over again.

To remedy the situation of CDC data being removed before before an Extract can process it, Oracle GoldenGate for SQL Server requires that a Purge Change Data task be created. This task will purge CDC staging data while ensuring that no data is purged that the Extract has yet to process.

Use the following steps immediately after enabling supplemental logging (TRANDATA) and prior to starting the Extract, to create the Oracle GoldenGate Purge Change Data task. The Purge Change Data task runs within the Administration Service and will automatically delete the SQL Server CDC Cleanup job when first created. There is no SQL Server Agent job for the Purge Change Data task as it is run by the Administration Service. Therefore, the Administration Service must be running in order for the cleanup task to function correctly.

To create a Purge Change Data task:

With Oracle GoldenGate for Microservices Architecture, after adding TRANDATA to tables but prior to starting Extract, a Purge Change Data task must be created to perform CDC cleanup on the database. This can be done through either one of the following:

Manual REST API requests
Administration Server Web UI

To create a Purge Change Data task using the Administration Service Web UI:

Click Configuration from the menu on the left to open the Configuration page.
Click Tasks from the Configuration page to open the Tasks page.
Click Purge Change Data from the Tasks page.
Click the plus sign to display a form, and fill out the required fields to create a new Purge Change Data task.
1. Operation Name: Name of the purge task to be created.
2. Enabled: Set the task to enabled, which is the default value.
3. Credential Domain and Credential Alias: Select an existing Credential Alias for the source database.
4. Keep Rule: This value determines in hours or days, the amount of CDC staging data to keep in the source database. Depending on the version of Oracle GoldenGate, the default values are either 3 days or 1 hour. Lower CDC data retention periods reduce the amount of CDC staging data stored in the database but limit the ability for a user to reposition the Extract back to a time older than the data that exists in the staging tables.
5. Purge Frequency: This value represents how often the task runs, with a default value of every 10 minutes. It is recommended to keep the default value unless overhead from the purge task is impacting database performance during periods of high user activity.

Note:

Only create one Purge Change Data task per source database.

Additional information of the Oracle GoldenGate CDC Cleanup job can be found in CDC Capture Method Operational Considerations.

Parent topic: Preparing the Source Database for Oracle GoldenGate Capture

CDC Capture Method Operational Considerations

Learn about the SQL Server CDC Capture options, features, and recommended settings.

Topics:

Parent topic: Transaction Log Settings and Requirements

Tuning SQL Server Change Data Capture

The following information is useful in improving the capture performance of the Extract.

Ensure that Auto Create Statistics and Auto Update Statistics are enabled for the database. Maintaining statistics on the cdc.OracleGG_#####_CT tables, cdc.lsn_time_mapping table, and OracleGGTranTables table are crucial to the performance and latency of the Extract.
The SQL Server Change Data Capture job collects data from the SQL Server transaction log and loads it into the Change Data Capture staging tables within the database.

As part of the job that is created, there are several available tuning parameters that can be used, and information on how to best tune the job can be found in the following article: https://technet.microsoft.com/en-us/library/dd266396(v=sql.100).aspx

As a general recommendation, you should change the SQL Server Change Data Capture Job polling interval from the default of 5 seconds to 1 second.

To change the default polling interval of the CDC Capture job, execute the following queries against the database:
```
EXEC [sys].[sp_cdc_change_job]
@job_type = N'capture’,
@pollinginterval = 1,
GO,
--stops cdc job
EXEC [sys].[sp_cdc_stop_job],
@job_type = N'capture’,
GO,
--restarts cdc job for new polling interval to take affect
EXEC [sys].[sp_cdc_start_job],
@job_type = N'capture’,
```

Parent topic: CDC Capture Method Operational Considerations

Oracle GoldenGate CDC Object Versioning

Oracle GoldenGate provides a version tracking subsystem to track the CDC objects that are created by Oracle GoldenGate when enabling supplemental logging. These objects are:

Oracle GoldenGate change tracking tables in the format OracleGG_object id_CT.
Stored procedures in the format fetch_database name_object id
Stored procedures OracleCDCExtract, OracleGGCreateProcs, and OracleGGCreateNextBatch.
After successfully completing the ADD TRANDATA command, Oracle GoldenGate creates a table called OracleGGVersion under the GGSCHEMA specified in the GLOBALS file, if it does not already exist.

Next, Oracle GoldenGate inserts a record into the table that tracks the start and end time of the TRANDATA session. When Extract starts up, it checks for consistency between itself and the Oracle GoldenGate CDC objects by comparing its internal version number with the version numbers found in the OracleGGVersion table. If it finds that the version numbers do not match, it abends with a message similar to the following:
ERROR OGG-05337 The Oracle GoldenGate CDC object versions on database, source, are not consistent with the expected version, 2. The following versions(s) were found: 1. Rerun ADD TRANDATA for all tables previously enabled, including heartbeat, heartbeat seed, and filter tables.

Parent topic: CDC Capture Method Operational Considerations

Valid and Invalid Extract Parameters for SQL Server Change Data Capture

This section describes parameters used for the CDC Capture method.

TRANLOGOPTIONS LOB_CHUNK_SIZE

The Extract parameter LOB_CHUNK_SIZE is added for the CDC Capture method to support large objects. If you have huge LOB data sizes, then you can adjust the LOB_CHUNK_SIZE from the default of 4000 bytes, to a higher value up to 65535 bytes, so that the fetch size is increased, reducing the trips needed to fetch the entire LOB.

Example: TRANLOGOPTIONS LOB_CHUNK_SIZE 8000

TRANLOGOPTIONS MANAGECDCCLEANUP/NOMANAGECDCCLEANUP

The Extract parameter MANAGECDCCLEANUP/NOMANAGECDCCLEANUP is used by the CDC Capture method to instruct the Extract on whether or not to maintain recovery checkpoint data in the Oracle GoldenGate CDC Cleanup job. The default value is MANAGECDCCLEANUP and it doesn’t have to be explicitly listed in the Extract. However, it does require creating the Oracle GoldenGate CDC Cleanup job prior to starting the Extract. MANAGECDCCLEANUP should be used for all production environments, where NOMANAGECDCCLEANUP may be used for temporary and testing implementations as needed.

Example: TRANLOGOPTIONS MANAGECDCCLEANUP

TRANLOGOPTIONS EXCLUDEUSER/EXCLUDETRANS

The SQL Server CDC Capture job does not capture user information or transaction names associated with a transaction, and as this information is not logged in the CDC staging tables, Extract has no method of excluding DML from a specific user or DML of a specific transaction name. The EXCLUDEUSER and EXCLUDETRANS parameters are therefore not valid for the CDC Capture process.

TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT/NOMANAGESECONDARYTRUNCATIONPOINT/ACTIVESECONDARYTRUNCATIONPOINT

The SQL Server Change Data Capture job is the only process that captures data from the transaction log when using the Oracle GoldenGate CDC Capture method. The secondary truncation point management is not handled by the Extract, and for the Change Data Capture Extract, these parameters are not valid.

TRANLOGOPTIONS ALWAYSONREADONLYROUTING

The ALWAYSONREADONLYROUTING parameter allows Extract for SQL Server to route its read-only processing to an available read-intent Secondary when connected to an Always On availability group listener.

TRANLOGOPTIONS QUERYTIMEOUT

Specifies how long queries to SQL Server will wait for results before reporting a timeout error message. This option takes an integer value to represent the number of seconds. The default query timeout value is 300 seconds (5 minutes). The minimum value is 0 seconds (infinite timeout). The maximum is 2147483645 seconds.

TRANLOGOPTIONS TRANCOUNT

Allows adjustment of the number of transactions processed per each call by Extract to pull data from the SQL Server change data capture staging tables. Based on your transaction workload, adjusting this value may improve capture rate throughput, although not all workloads will be positively impacted. The minimum value is 1, maximum is 100, and the default is 10.

Parent topic: CDC Capture Method Operational Considerations

Details of the Oracle GoldenGate CDC Cleanup Process

The Oracle GoldenGate Purge Change Data task is required for a CDC Extract by default, since Extract defaults to TRANLOGOPTIONS MANAGECDCCLEANUP.

There should only be one purge task for each database enabled for CDC Capture, and you must create the task using the steps mentioned in the Preparing the Source Database for Oracle GoldenGate Capture section of this document.

Modifying the Oracle GoldenGate Purge Change Data Task

The default purge task frequency schedule, and data retention period for the Oracle GoldenGate Purge Change Data task is to run every 10 minutes, with a data retention policy of 3 days or 1 hour, depending on the version of Oracle GoldenGate installed.

For customer specific requirements, it may be necessary to adjust the retention period (Keep Rule option) and the task run-time schedule (Purge Frequency option).

The Keep Rule option determines in hours or days, the amount of CDC staging data to keep in the source database. Depending on the version of Oracle GoldenGate installed, the default values are either 3 days or 1 hour. Lower CDC data retention periods reduce the amount of CDC staging data stored in the database but limit the ability for a user to reposition the Extract back to a time older than the data that exists in the staging tables. Typically, there would be no need to reposition an existing Extract back to an earlier point in time, so it is recommended to use the newer default setting of 1 hour unless there is a specific case that requires more staging data to remain in the database. Note that though if you change this value from a higher retention period to a very short retention period, the next time the task schedule runs, it could consume a lot of transaction log space and system overhead. So it is recommended to slowly decrease the Keep Rule value over time, until you reach the desired ending value.

The Purge Frequency represents how often the task runs, with a default of every 10 minutes. It is recommended to keep the default value unless overhead from the purge task is impacting database performance during periods of high user activity.

To modify an existing Purge Change Data task, navigate to the Configuration from the menu on the left of the Administration Service, to open the Configuration page.

Click Tasks from the Configuration page to open the Tasks page.
Click Purge Change Data from the Tasks page.
Click the Alter Task icon next to an existing task.
Modify the values of Keep Rule and Purge Frequency options as required.
Click Submit to save the changes.

Deleting the Oracle GoldenGate Purge Change Data Task

Deleting a Purge Change Data task for a database should only be done if there are no Extracts configured to capture against the specific database.

To delete an existing Purge Change Data task, navigate to the Configuration option from the menu on the left of the Administration Service, to open the Configuration page.

Click Tasks from the Configuration page to open the Tasks page.
Click Purge Change Data from the Tasks page.
Click the Delete Task icon next to the task to be removed.

Parent topic: CDC Capture Method Operational Considerations

Changing from Classic Extract to a CDC Extract

If you plan to change from using a Classic Extract from Oracle GoldenGate 12c (12.3.0.1) or earlier, to an Oracle GoldenGate 21c CDC Extract, then you must remove the supplemental logging that was implemented using the Classic Extract installation method, and re-enable supplemental logging using the CDC Extract installation binaries, as the calls to enable TRANDATA are different between the two versions, and the implementation of TRANDATA for Classic Extract is not supported by the CDC Extract.

Follow these general guidelines to remove and re-enable supplemental logging. Special consideration and planning should be involved if migrating from Classic to CDC Extract in a production system. The information provided here does not cover all requirements and is only offered as general requirements regarding supplemental logging:

Ensure that the Classic Extract has processed all remaining data in the logs and can be gracefully stopped.
Do one of the following, depending on how Extract was running in relation to other replication or CDC components:
- If Extract was not running concurrently with SQL Server transactional replication or a non-Oracle CDC configuration on the same database, open a query session in Management Studio and issue the following statement against the source database to disable and delete any CDC or replication components, and to clear the secondary truncation point.
  
  EXEC sys.sp_cdc_disable_db
- If Extract was running concurrently with SQL Server transactional replication or a non-Oracle CDC configuration on the same database, run GGSCI from the Classic Extract’s installation folder, login to the source database with the DBLOGIN, and then issue the following command for each table that is in the Extract configuration. You can use a wildcard to specify multiple table names
  
  DELETE TRANDATA owner.table
  
  DELETE TRANDATA owner.*
Delete any heartbeat table entries if one was installed.

DELETE HEARTBEATTABLE
Using the Oracle GoldenGate CDC Extract installation binaries, follow the steps listed in Preparing the Source Database for Oracle GoldenGate Capture to re-enable supplemental logging and other necessary components, and re-add the heartbeat table.

Parent topic: CDC Capture Method Operational Considerations

Requirements Summary for Capture and Delivery of Databases in an Always On Availability Group

Oracle GoldenGate for SQL Server supports capture from a primary replica or a read-only, synchronous mode secondary replica of an Always On Availability Group, and delivery to the primary replica.

When capturing from either a primary or a secondary replica in an Always On Availability Group, it is important to understand that the capture process must only read hardened transactions from the log, and that there be no potential for data loss between any replica database that Oracle GoldenGate is or will capture from.

Topics:

Parent topic: SQL Server

Database Connection

For both Extract and Replicat, it is recommended to create a System DSN that uses the Always On Availability Group Listener for the connection.

For the Replicat, connecting to the Listener allows the Replicat to reconnect if the primary replica performs a failover to a new instance, without having to manually edit the DSN settings to point to the new primary.
For the Extract connecting to the Listener not only allows reconnecting to the primary without editing the DSN to point to the new instance, but also provides the optional ability to run the Extract’s data extraction stored procedures, against a read-only secondary.
For both Extract and Replicat connected to an Always On environment, use the AUTORESTART parameter for the Manager, to restart the processes after a failover.
To route the Extract’s data extraction queries to a read-only secondary, ensure that the DSN connection uses the Listener, that you have one or more read-only secondary replicas that are configured to handle read-only routing, and that the Extract runs with the TRANLOGOPTIONS ALWAYSONREADONLYROUTING parameter.
- Ensure that the Application Intent field of the DSN configuration is set to READWRITE and not READONLY
- Refer to the following Microsoft documentation on how to configure read-only routing: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server?view=sql-server-2017

Parent topic: Requirements Summary for Capture and Delivery of Databases in an Always On Availability Group

Supplemental Logging

Supplemental logging must be enabled by normal means (ADD TRANDATA) using Admin Client connected to the primary replica and not against a secondary replica.

Create a DSN to the primary replica, or to the Always On Availability Group Listener, to connect using DBLOGIN to run ADD TRANDATA.
The login used to enable supplemental logging must have sysadmin membership of the primary replica instance.
When enabling supplemental logging against the primary replica database, the SQL Server Change Data Capture job does not automatically get created on any secondary replicas. Upon failover from a primary to a secondary, you must manually create the SQL Server Change Data Capture job and the Oracle CDC Cleanup job if in use, on the new primary replica.

EXECUTE sys.sp_cdc_add_job N'capture
- When creating the SQL Server CDC Capture job on the new primary, the default configuration settings are put in place. So if you have previously modified the default values on the former primary replica, you need to run sys.sp_cdc_change_job on the new primary and set the values accordingly.

Note:

Consult the Microsoft documentation on how to enable the CDC Capture job for AlwaysOn Secondary Replicas for more information.

Parent topic: Requirements Summary for Capture and Delivery of Databases in an Always On Availability Group

Operational Requirements and Considerations

When an instance is no longer the primary instance but has the SQL Server CDC Capture job installed, the job ceases to run after some time and does not attempt to restart. Upon failover back to that instance, the job does not automatically start, so it must be manually started.
If secondary replica databases are not in sync with the primary replica database, the CDC capture job will not advance in the log, and therefore no records will be captured by an Extract, until such time that the primary and secondary replicas are synchronized. See this article from Microsoft for more details:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability?view=sql-server-2017

Note:
When capturing from either a primary or a secondary replica in an Always On Availability Group, it is important to understand that the capture process must only read hardened transactions from the log, and that there be no potential for data loss between any replica database that Oracle GoldenGate is or will capture from.
When running an Extract from a middle tier Windows or Linux server, set the middle tier server's date, time, and time zone to the same as that of the primary replica.
Upon failover from a primary to a secondary replica, reinstall the Oracle GoldenGate CDC Cleanup job on the new primary by re-running the ogg_cdc_cleanup_setup.bat file with the createJob option.
If Extract is configured to capture from a readable secondary replica, but not configured with read-only routing, the SQL Server CDC Capture job must be created against the secondary replica prior to starting the Extract, as the Extract will check if the job exists. To create the SQL Server CDC Capture job, any potential secondary that will have an Extract connected to it, must at some point be set to a writable Primary database and then follow the steps above, under supplemental logging, to manually add the SQL Server CDC Capture job.
If uninstalling Oracle GoldenGate and disabling Change Data Capture on a database that is part of an Always On availability group, follow the extra steps provided in Disabling Change Data Capture.

Parent topic: Requirements Summary for Capture and Delivery of Databases in an Always On Availability Group