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 DR 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 Reader |
Log Server Agent |
Advantages |
Simple
to configure 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.
|
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 DR
goes down. It requires the database to retain physical log files
for shorter periods. |
Disadvantages |
· Query performance decreases as the number of replications or groups increases because each replication or group requires Syniti DR 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 DR 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 DR. · If Syniti DR
goes off line, the database also has to retain physical log files
until Syniti DRrestarts and queries all the changes. |
· Additional file management: an extra application, the Log Server Folder, and Log Files Folder. 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 DR 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
This option is available only when replicating from IBM Db2 for i, and is specifically designed for replications that include Large Object Binaries.