21 Oracle GoldenGate Classic Extract for SQL Server Standard Edition Capture

Oracle GoldenGate for SQL Server includes a Classic Capture support for SQL Server Standard Edition. Oracle GoldenGate utilizes certain SQL Server Replication components in order to enable supplemental logging. These SQL Server Replication components are required to be installed and configured in order to enable supplemental logging, and the instructions and limitations are outlined in the following sections.

SQL Server Replication components must be installed. This is normally done with the initial installation of SQL Server. If its not already installed, you can re-run the SQL Server installer and add that feature to the existing instance. To install SQL Server Replication components, perform the following steps:

1. On the SQL Server Installation Center screen, select the New installation or add features to an existing installation option.

2. On the Installation Type page, select the Add features to an existing instance of SQL Server option.

3. Select the developer features to install on the feature selection page.

Once the SQL Server Replication components are installed, a Distributor must be configured, along with a distribution database. These steps must be done manually prior to attempting to enable Supplemental Logging for any table in the database.

The Distributor can be a local or a remote Distributor, and can be one that has already been configured for an existing SQL Server Replication implementation. Oracle GoldenGate does not require the distribution database to store change data, but it must be configured in order to enable supplemental logging.

When enabling supplemental logging for tables within SQL Server Standard Edition, the tables must contain a Primary Key.

When enabling supplemental logging for tables within SQL Server Standard Edition, a new SQL Server Replication Publication is created, and the tables enabled for supplemental logging will be added to the Publication as Articles.

If there are no previous Publications for the database, a new Log Reader job will be created under SQL Server Agent, and depending on the secondary truncation point management method used by Extract, the job may need to be manually stopped and disabled. The created Publication has the naming convention of: "[DatabaseName]: GoldenGate DatabaseName Publisher".

Here is an example of how to manually stop the SQL Server reader job.

The Article properties of the tables to be configured with supplemental logging will not log data changes to the distribution database, but the creation of the Publication with Articles is the requirement to enabling supplemental logging.

• If Oracle GoldenGate Capture will not be used in conjunction with other SQL Server Transactional Publications for the source database, it is recommended to let the Extract manage the secondary truncation point by one of the two available options listed in See Reference..

• Allowing Oracle GoldenGate to manage the secondary truncation point requires that you manually stop and disable the database Log Reader job SQL Server Agent.

• The database Log Reader job will have a similar naming convention as Server \Instance-DatabaseName-1and will be of the job category REPL-LogReader when its properties are viewed.

• To stop and disable the job, right-click the job name under SQL Server Agent within Management Studio then select Stop Job, follow the prompts to stop the job, then right-click again, and then click Disable.