Microsoft SQL Server CDC Connector

Back Next

Handling changes to extraction maps

If you add or drop columns in a Microsoft SQL Server source table, you must create a new capture registration. PowerExchange generates an associated extraction map by default. Use the generated extraction map instead of creating a user-defined extraction map. The PWX CDC Reader on the MySQL CDC Connector system will retrieve information from the new generated extraction map.

To switch to the new table definition and preserve access to previously captured data, you must perform several steps in both the PowerExchange and SQL Server CDC Connector environments.

You do not need to perform these steps if you selectively capture change data for a subset of columns, and the DDL changes do not affect any of these columns.

Stop data change activity on the SQL Server source table.

Verify that any change data that was captured under the current table definition has completed SQL Server CDC Connector extraction processing. Then stop all mapping tasks that extract change data for the table.

Shut down the PowerExchange Logger for Linux, UNIX, and Windows.

In the PowerExchange Navigator, open the original capture registration and set its status to

History

Add or drop columns in the source table.

In the PowerExchange Navigator, create a new capture registration for the table that reflects the column changes. Include the following settings:

In the

Condense

list, select

Part

In the

Status

list, select

Active

Warm start the PowerExchange Logger.

Alter the target table to reflect the source table changes, if necessary.

In Data Integration, for each mapping that includes the source table, perform the following steps:

Select the Source transformation. Under Source Properties, either click the

Synchronize

button on the

Fields

tab or select the table again in the

Object

field on the

Source

tab. Either action causes the PWX CDC Metadata Adapter to retrieve the updated extraction-map metadata.

If you changed the target table, select the Target transformation. Under Target Properties, click the

Synchronize

button on the

Target Fields

tab or select the table again in the

Object

field on the

Target

tab.

Edit the field mapping to reflect the added or removed columns.

If necessary, rematerialize the target tables.

In the mapping task, set a time-based restart point that coincides with the time of target rematerialization or let the mapping task restart from the last checkpoint that was recorded in the state table on the target.

Resume change activity on the source table.

Start the mapping task again.

Microsoft SQL Server CDC administration

Download Guide

Watch

Comments

Cloud Connectivity Homepage

Communities

Knowledge Base

Success Portal