1. Oracle GoldenGate Classic Documentation
  2. Prepare
  3. Prepare Your Database for Oracle GoldenGate Classic Architecture
  4. SQL Server
  5. Database Connectivity
  6. Configuring a Replicat Database Connection
  7. Connectiong with the OLE DB USEREPLICATIONUSER Option

Connectiong with the OLE DB USEREPLICATIONUSER Option

If Replicat connects as the SQL Server replication user through OLE DB with the USEREPLICATIONUSER option, and NOT FOR REPLICATION is enabled for IDENTITY columns, triggers with foreign key constraints, the following benefits and limitations apply.

  • IDENTITY seeds are not incremented when Replicat performs an insert. For SQL Server bidirectional configurations, stagger the seed and increment values like the example in Step 3 of the previous section.

  • Triggers are disabled for the Replicat user automatically on the target to prevent redundant operations. However triggers fire on the target for other users.

  • Foreign key constraints are not enforced on the target for Replicat transactions. CASCADE updates and deletes are not performed. These, too, prevent redundant operations.

  • CHECK constraints are not enforced on the target for Replicat transactions. Even though these constraints are enforced on the source before data is captured, consider whether their absence on the target could cause data integrity issues.

    Note:

    Normal IDENTITY, trigger, and constraint functionality remains in effect for any users other than the Replicat replication user.

To use Replicat with USEREPLICATIONUSER, follow these steps:

Note:

This feature is only supported for Oracle GoldenGate on Windows.

Note:

Install the Microsoft OLE DB Driver for SQL Server software on the Oracle GoldenGate server.

https://docs.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15

  1. In SQL Server Management Studio (or other interface) set the NOT FOR REPLICATION flag on the following objects. For active-passive configurations, set it only on the passive database. For active-active configurations, set it on both databases.

    • Foreign key constraints

    • Check constraints

    • IDENTITY columns

    • Triggers (requires textual changes to the definition; see the SQL Server documentation for more information.)

  2. Partition IDENTITY values for bidirectional configurations.
  3. 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. 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.

  4. In the Replicat parameter file, include the DBOPTIONS parameter with the USEREPLICATIONUSER option.