You must perform a few configuration tasks to prepare SQL Server for PowerExchange change data capture (CDC).
If your SQL Server tables have a high level of update activity, use a distributed server as the host of the distribution database from which to capture change data. This practice prevents competition between PowerExchange CDC and your production database for CPU use and disk storage.
PowerExchange uses SQL Server Management Objects (SMO) to manage SQL Server publications. SMO is a .NET Framework object model that applications can use to manage and administer SQL Server objects and services. You must install SMO and some related packages before you create or edit capture registrations. PowerExchange provides the 64-bit installation packages for the required SMO objects and packages for your convenience.
On the system where you run the PowerExchange Navigator and PowerExchange utilities, install the following SQL Server 2008 SMO objects and packages by using the installer .msi files in the
PowerExchange_root_installation
\packages\mssqlInstalls\2008
directory:
Microsoft SQL Server 2008 Management Objects
Microsoft SQL Server 2008 Replication Management Objects
Microsoft SQL Server 2008 Native Client
Microsoft SQL Server System CLR Types
You must install the Microsoft SQL Server 2008 Replication Management Objects even if you use SQL Server 2012 or SQL Server 2014.
Start the SQL Server Agent and Log Reader Agent if they are not running. For more information, see your Microsoft SQL Server documentation.
Configure and enable SQL Server transactional replication on the publication database. For more information, see your Microsoft SQL Server documentation.
The default transactional retention period at the Distributor is 72 hours. If you are using the PowerExchange Logger, accept this default retention period. If you are not using the PowerExchange Logger, Informatica recommends that you increase the retention period to 14 days. However, you might need to decrease the value if you have a high volume of transactions or space constraints.
Verify that each source table in the distribution database has a primary key.
