|Product:||Syniti Data Replication, DBMoto|
|Summary:||Trigger-based transactional replications use a single master log table per connection. With many replications and a high number of transactions, log tables may be locked when accessed by Syniti DR/DBMoto. Create multiple master log tables to avoid this situation.|
Trigger-based replications use a master log table to record general information about transactions, like user name, timestamp, table name. One master log table is used per source connection, and one log table per source table. In some situations, the master log table/log tables can become a bottleneck for the replication process. For example, if a single replication group contains many table replications, all the replications are using the same connection to the source database, the same master log table and, at the same time, accessing the log tables to record transactions. Customers have experienced a situation where a log table generates a lock error because of the access load. The best way to avoid this situation is to break large replication groups into smaller groups by creating multiple connections to the source database. This allows you to create and use multiple master log tables, one for each connection, and consequently lessens the overall data access load for each replication group.
Here are the steps to set up smaller replication groups with multiple master log tables.
- Decide how to break out your large group into a number of smaller groups.
- In the Syniti DR/DBMoto Management Center, create a number of new source connections, identical except for the name. The number of source connections should match the eventual number of groups.
- For each group, right click on the group name and choose Connection Properties.
- In the Transaction Log Type field, click the three dots … at the end of the row.
- In the Setup Info dialog, locate the Master Table field and click Change.
- In the Change Master Table dialog, select the Add New Master Table option.
- Manually enter a name for the the Master Table. For example, _DBM__MASTERLOG1.
- Click OK.
- Repeat the above steps for each connection, using a new master table name each time.
- After all the connections have been set up, specify the tables for each connection by selecting the connection, then, from the right mouse button menu, Select Tables.
- Create a group for each connection.
- Create the replications for each connection as usual and add the replications for each connection to the groups you have created. For example, if you created 6 connections, you create 6 groups and put all the replications under the connection into a group.
As a result, the replications will use several different connections and each connection will use its own master table.