Skip to main content

Switching from initial load to continuous replication of SQL Server sources

When you use an automated pipeline wizard, switching from initial load to continuous replication is handled automatically. In all other cases, you must configure the switch manually.

Switching from initial load to continuous real-time replication of SQL Server sources

Do the following to make continuous real-time replication pick up where the initial load left off.

Before running the initial load application, get the current maximum log sequence number. Run the following command on the source database using an appropriate database client and record the value returned:

SELECT sys.fn_cdc_get_max_lsn()AS max_lsn;

When creating the continuous real-time replication application:

  1. Enable recovery.

  2. In MSSSQL Reader or MSJet, set Start Position to the LSN you recorded before performing the initial load .

  3. If the target writer has an Ignorable Exception Code property, set it as follows:

    DUPLICATE_ROW_EXISTS, NO_OP_UPDATE, NO_OP_DELETE

  4. If the target writer has an Ignorable Exception Code property, when you know all open transactions have completed and been written to the target, stop and undeploy the application, edit the target, clear the Ignorable Exception Code value, save, and deploy and start the application. Since recovery is enabled, writing will resume where it left off, and there should be no missing or duplicate rows.

Switching from initial load to continuous incremental replication of SQL Server sources

Do the following to make continuous incremental replication pick up where initial load left off:

  1. After initial load completes, query the target to get the highest check column value.

  2. In Incremental Batch Reader, set the Start Position value to the next valid value after the highest. For example:

    • If the check column is of type integer and the highest check column value in the target is 100000, set Start Position to 100001.

    • If the check column is of type timestamp and its precision is milliseconds, set Start Position to one millisecond after the highest check column value in the target.