Choosing a business goal for the replication asset

Business goal settings

You can configure your replication asset to use one of the following types of business goal settings:

Copy

The Copy business goal configures the replication asset to synchronize the specified schemas and tables from the source database to the target database.

Change Log

The Change Log business goal configures the replication asset to document and track modifications that are made to a source database schema over time in a record called a change log. In addition, the replication asset synchronizes schemas and tables from the source to the target database. A copy of the changes to the source database and the change log are delivered to the target database. For more information about how to customize your change log settings, see Configuring the Change Log business goal. Use the Change Log business goal to preserve consistency, guarantee compatibility with earlier versions of your data, and ease schema migrations between various environments, such as development, testing, and production.

CDC Replication

The CDC Replication business goal configures the replication asset to use a Data Replication Access Server connection to configure and operate Change Data Capture (CDC) subscriptions by connecting the Data Replication service to a remote CDC replication deployment.

Differences between the Copy scenario and the Change Log scenario

The Copy scenario maintains an identical copy of the source table at the target database. The Change Log scenario maintains a history of the flow of operations, both DML and DDL, that are performed on the source database. In the Change Log scenario, the details of the changes are recorded along with the actual changes in the target table.

The three significant differences between the Copy scenario and the Change Log scenario are:

Primary keys

Multiple instances of the same key can appear in the Change Log scenario, which means that something that is a primary key in a Copy scenario becomes a simple index in a Change Log scenario.

See the following example, where multiple rows in the CHANGE_LOG table have the same primary key value as the source table.

Handling of before and after images

Insert records have no before, so the target database has no insert operation for a before image.

Delete records have no after, so the target database has no insert operation for an after image.

Update records have both before and after segments, so the target database has both images inserted.

See the following example to see how before and after images are handled for DMLs in the Change Log.

Table structure

The Change Log scenario expands before and after columns or rows based on the Change Log type.

In the Copy scenario, the target table structure is the same as the original table structure.

See the following example, where the CHANGE_LOG table provides more information about the operations that were performed. For the Copy scenario, both source and target tables have identical data and structure.

Example:

If you want to replicate data using the delete_insert_pair Change Log type for the following SQL operations:

INSERT INTO SOURCE_TABLE (ID, NAME) VALUES (1, 'abc');
INSERT INTO SOURCE_TABLE (ID, NAME) VALUES (2, 'xyz');
UPDATE SOURCE_TABLE SET NAME ='upd' WHERE ID = 1;
DELETE FROM SOURCE_TABLE WHERE ID = 2;

The following table is your source table and ID is the primary key:

For a Copy scenario, the target table would look like:

For a Change Log scenario with the SQL query:

SELECT ID, NAME, OPTYPE, SEQUENCE, TRANSACTION_ID FROM TARGET_TABLE ORDER BY TRANSACTION_ID ASCENDING, SEQUENCE ASCENDING

the target table would look like:

The target table contains additional columns such as SEQUENCE, OPTYPE, TRANSACTION_ID with information about the history of operations.

Compatible source and target databases based on business goal

Make sure that your chosen source and target can be used for your chosen business goal:

Source-target database combinations with a Change Log

You can use the Change Log business goal for the following combinations of source and target data stores:

Learn more

• Configuring the Change Log business goal