CDC Guide for Linux, UNIX, and Windows

10.5
- 10.5.6
- 10.5.5
- 10.5.4
- 10.5.3
- 10.5.2
- 10.5.1
- 10.4.1
- 10.4.0 HotFix 1
- 10.4.0

Back Next

SQL Server CDC Operational Considerations

PowerExchange for SQL Server CDC has the following operational considerations:

PowerExchange can capture change data from SQL Server distribution databases for which Transparent Data Encryption (TDE) is enabled. No special configuration tasks are required.

PowerExchange uses the DataDirect ODBC driver for Microsoft SQL Server to create capture registrations and capture change data from the distribution database. The PowerExchange installation provides the ODBC driver in the

pwx_base_installation\ODBCversion\Drivers

directory. No configuration of the driver is required.

PowerExchange does not capture change data for SQL Server system tables.

The maximum length of a row for which PowerExchange can capture and process change data is 128,000 bytes.

PowerExchange does not capture the user ID that is associated with the original transaction that updated the database.

The timestamp that PowerExchange records for each captured change indicates when the change was written to the distribution database by the SQL Server Replication Log Reader, not when the original transaction occurred.

PowerExchange does not capture change data for derived columns that are not persisted. SQL Server computes values for these columns at run-time based on an expression but does not store the values in a table.

SQL Server publishes deferred updates
to SQL Server tables as DELETEs followed by INSERTs rather than as UPDATEs. Consequently, PowerExchange propagates deferred updates as DELETEs followed by INSERTs, even if you select

for the

Image Type

attribute in the CDC connection. PowerExchange does not include before image (BI) and change indicator (CI) information in DELETE and INSERT records. If you must capture a deferred update as an UPDATE for business reasons, set the SQL Server 8207 trace flag. This flag causes the SQL Server Replication Log Reader to combine the DELETE and INSERT pair into a single UPDATE. For more information about SQL Server processing of deferred updates and the SQL Server 8207 trace flag, see the SQL Server documentation

PowerExchange does not support the use of local aliases when connecting to SQL Server and creating publications at registration creation.

If you need to switch the status of multiple SQL Server capture registrations from active to inactive or from inactive to active, use the DTLUCBRG utility with the MSSOPTS UPDATESTATUS parameter. This optional parameter enables you to switch the status of many registrations in one operation and regenerate the associated SQL Server publications.

If you run the PowerExchange Listener on a Linux system or on a Windows system that is remote from the system where the SQL Server distribution database runs, you must define a MSQL CAPI CONNECTION statement that provides connection information for the SQL Server distribution database in the DBMOVER configuration file on the Listener system. Also, in the DBMOVER configuration files on the PowerCenter Integration Service machine and the PowerExchange Navigator system, define a NODE statement that points to the PowerExchange Listener system. To connect to the SQL Server system, the PowerExchange Listener uses the DataDirect ODBC driver that PowerExchange supplies.

If columns are added with the NOT NULL and DEFAULT options to a Microsoft SQL Server 2012 or later source table, PowerExchange adds an appropriate default value, which is based on the column datatype, to the captured before image so that extraction processing can continue. PowerExchange does not process the added columns until a subsequent Update or Delete occurs on the source. You cannot change the default values that PowerExchange uses. If the use of the default values is not acceptable in your environment, you must rebuild the source table after the columns are added.

PowerExchange CDC use of SQL Server transactional replication does not support column-level collation settings on CHAR, VARCHAR, and TEXT columns with a code page that is different from that of the database collation. To prevent change data loss or corruption, either do not use column-level collation or change the CHAR, VARCHAR, and TEXT column datatypes to NCHAR, NVARCHAR, or NTEXT.

If the license.key file on the PowerExchange Navigator machine does not authorize use of SQL Server CDC, when you try to create a capture registration for a SQL Server data source, the PowerExchange Navigator fails with a license key error. In this case, go the Informatica Network at https://network.informatica.com and access eSupport. Then create a case of type=shipping to request a key that includes SQL Server CDC.

PowerExchange can capture changes that are written to an availability database in a SQL Server Always On Availability Group. An Availability Group consists of primary and secondary replica databases on multiple nodes in a Windows Server Failover Clustering (WSFC) cluster. Only the following configuration has been tested and certified for PowerExchange CDC:

The distribution database is installed on a node outside of the Always On Availability Group cluster, which is consistent with SQL Server requirements.

PowerExchange is installed on a node outside of the Always On Availability Group cluster.

When you create a registration group for the SQL Server Always On Availability Group source, you specify the Availability Group listener name in the

Database Server

field.

If you need to use another configuration, contact Informatica Global Customer Support. Informatica will try to accommodate your request.

After CDC processing is running, if the primary database fails over to a secondary replica database on another node, PowerExchange can continue to capture change data from the distribution database without data loss.

PowerExchange uses the DataDirect ODBC driver for SQL Server to connect to Microsoft SQL Server source databases. You can define the ODBC_CONN_PARAMS statement in the dbmover.cfg file If you want to add ODBC parameters to the connection strings that are used on the servers you specify for the following processing:

Change data capture

Creating, deleting, or modifying capture registrations from the PowerExchange Navigator or DBLUCBRG utility

For example, you might need to add ODBC parameters if your site policies require database connections to use SSL encryption, specific cryptographic protocols, or self-signed or third-party signed SSL certificates. For more information, see the PowerExchange Reference Manual
.

PowerExchange does not capture changes from SQL Server views.

Set the CAPI_CONNECTION parameter GUIDBRACES=N to remove braces {} around GUID values in data captured from registered SQL Server columns that have the uniqueidentifier datatype.

Planning for SQL Server CDC

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal