1. Microservices Architecture Documentation
  2. Prepare
  3. Prepare Databases
  4. SQL Server
  5. Preparing Tables for Processing

Preparing Tables for Processing

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

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.

  • For tables on the target database that have triggers, set the SET XACT_ABORT parameter to off. This ensures that execution of the trigger operation does not result in missing transactions.

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.

    • Disable or drop the Identity property, triggers and constraints on the target tables.

    • 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.

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.

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:

  1. Primary key

  2. First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.

  3. 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.

  4. 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

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.