Choosing a Log Type for Transactional Replications

The Enable Transactional Replication wizard allows you to configure access to data changes on a source database (for mirroring and synchronization) or a target database (for synchronization). Depending on the source database, available options for identifying data changes are:

Log Reader

Reads the native database transaction log using a Syniti Replicate reader thread for each replication. This option is the default choice and works well if you have a limited number of replications and do not want to install additional services on the system that is running the Server Agent component.

Log Server Agent

Creates a Windows service on the system running the Server Agent that makes only a single connection to the database to read the binary log for all replications in a source connection. The service autonomously reads the native database transaction log and writes all changes from the tables being replicated into intermediate binary log files (a Syniti proprietary format) in a specified folder. The Replication Agent reads the changes from the intermediate binary log files.

Choose this option if you plan to add many replications and you need a scalable solution that optimizes access to the native database transaction log.

Below is a summary of the advantages and disadvantages of replicating the Log Reader transactional replication mode versus using the Log Server Agent transactional replication mode:

Log Type	Advantages	Disadvantages
Log Reader	Simple to configure Standalone configuration Each replication connects directly to the source database and does not rely on other components - which at times may be stopped or in error or in any other state.	Query performance decreases as the number of replications or groups increases because each replication or group requires Syniti Replicate to create a reader thread to query the database for changes. For example, if a data source has 2 single replications and 10 group replications, Syniti Replicate would need to create 12 threads to query the database for data changes. As performance decreases, the database has to retain physical log files longer until all changes are returned to Syniti Replicate. If Syniti Replicate goes off line, the database also has to retain physical log files until Syniti Replicate restarts and queries all the changes.
Log Server Agent	Performance The Log Server only uses ONE instance of the native database log reader to query for data changes - regardless of the number of replications and groups. The Log Server queries the database for all changes in a data source at the same time and then stores them in intermediate log files. The replications and groups then read changes from the intermediate log files (very efficient.) Less physical log file retention required Because the Log Server runs continuously in the background, it can capture data changes more rapidly, and it stays running independently even when Syniti Replicate goes down. It requires the database to retain physical log files for shorter periods.	Additional File Management: An extra application, the Log Server Folder, and Log Files Folder need to be managed manually. However, the application management tasks are not complex.

Log Type

Advantages

Disadvantages

Log Reader

Simple to configure Standalone configuration

Each replication connects directly to the source database and does not rely on other components - which at times may be stopped or in error or in any other state.

Query performance decreases as the number of replications or groups increases because each replication or group requires Syniti Replicate to create a reader thread to query the database for changes. For example, if a data source has 2 single replications and 10 group replications, Syniti Replicate would need to create 12 threads to query the database for data changes.
As performance decreases, the database has to retain physical log files longer until all changes are returned to Syniti Replicate.
If Syniti Replicate goes off line, the database also has to retain physical log files until Syniti Replicate restarts and queries all the changes.

Log Server Agent

Performance
The Log Server only uses ONE instance of the native database log reader to query for data changes - regardless of the number of replications and groups. The Log Server queries the database for all changes in a data source at the same time and then stores them in intermediate log files. The replications and groups then read changes from the intermediate log files (very efficient.)
Less physical log file retention required
Because the Log Server runs continuously in the background, it can capture data changes more rapidly, and it stays running independently even when Syniti Replicate goes down. It requires the database to retain physical log files for shorter periods.

Additional File Management: An extra application, the Log Server Folder, and Log Files Folder need to be managed manually. However, the application management tasks are not complex.

Triggers

Creates a trigger on the database for each replication to log data from committed transactions. This option is useful when neither the Log Reader or the Log Server Agent meet the needs of your environment. For example, use triggers when:

There are no primary keys and the source is Microsoft SQL Server.
All column data (before and after image) is needed. Some standard log readers only return record changed values
Performance is better because only relevant information is logged and Syniti Replicate does not read the entire database log.
The source database is MySQL versions prior to 5.1.5 or you are preforming MySQL synchronization replications.

Log Reader API

Note
As of Syniti Replicate version 10.7.x, the Log Reader API is deprecated.

This option is available only when replicating from IBM Db2 for i, and is specifically designed for replications that include Large Object Binaries.