In this article, I will explain different scenarios to replicate your data. Syniti Replicate could provide flexibility to create complex data replication schemas using multiple tables and heterogenous databases at the same time.
Those are the table-base replication scenarios:
- One to One Table Replication
- One to Many Tables Replication
- Many to One Table Replication
- Many to Many Tables Replication
Replication in Syniti Replicate is defined at the table level, so there is a replication definition for each source-target table pair. To replicate an entire database, see Database Replication Scenarios.
One to One Table Replication
This is the simplest case where a source table is replicated to a target table that may be on the same or a different database server. For this type of replication, you need to define a source database connection and a target database connection. Then you need to specify replication details that include the type of replication, the scheduling and the mapping between source and target fields in the table. You can also write a script to transform values in the source table for the target table.
One to Many Tables Replication
In this scenario, a source table is replicated to more than one target table where the target tables may be on the same or a different database server. For this type of replication, you need to define a source database connection and one or more target database connections.
You will need a connection for each database server. Then you need to specify replication details for each source-target pair that include the type of replication, the scheduling and the mapping between source and target fields in the table.
You can also write a script to transform values in the source table for the target table. For example, in the Customers source table, you might replicate all the customer data to target table A, but just the customer ID and the fields containing financial information to target B. In this example, you would need the following source and target connections.
Many to One Table Replication
In this scenario, multiple source tables are replicated to a single target table. For this type of replication, you need to define source database connections and a target database connection. You need to specify replication details for each source-target pair that include the type of replication, the scheduling and the mapping between source and target fields in the table. You can also write a script to transform values in the source table for the target table.
Syniti Replicate does not provide tools to manage possible conflicts when replicating to a table from different sources. When planning the replications, you need to avoid possible conflicts.
In replicating two different source tables to a single target table, you would need the following source and target connections.
Many to Many Tables Replications
In this scenario, multiple source tables are each replicated to one or more target tables. Carefully analyze the relationships for each source-target pair and create source and target connections based on the descriptions above in Many to One Table Replication and One to Many Tables Replication.
Syniti Replicate does not provide any specific features for handling this type of scenario. In this example, you would need the following source and target connections.
Database Replication Scenarios
Database replications typically fall into one of the following categories.
One to One Database Replication
The easiest and most common scenario involves one source database and one target database, where Syniti Replicate reads data from the source database or log/journal, and writes to the target database.
The replication may involve one table or several tables. If the replication involves more than one table, you can set up replications for each table quickly and efficiently using the Multiple Replications wizard. See also Table Replication Scenarios for additional scenarios.
For this type of replication, you will need to define one source connection and one target connection before defining replication details for each table involved in the replication. Replication details depend on whether you intend to perform a refresh replication, perform synchronization, or set up mirroring from the source to the target database.
One to Many Databases Replication
In this scenario, data from a single source database must be replicated to multiple target databases. The replication may involve one table or several tables on the source and target sides. See Table Replication Scenarios for more information.
For this type of replication, you will need to define one source connection and one target connection for each database to which you are replicating data. You will also need to define replication details for each table involved in the replication.
For example, if you have two tables on the source database, one of which needs to be replicated to a SQL Server target database, and the other to an Oracle target database, you will need to set up two replications. Replication details depend on whether you intend to perform a refresh replication, perform synchronization, or set up mirroring from the source to the target database.
This approach could be useful in the following conditions:
- Department databases are present
- Security policies require several target databases
- You need to reduce network traffic by replicating data only where necessary
Many to One Database Replication
This scenario often occurs when consolidating enterprise data. You will need to define one source connection for each database from which you are replicating. If you are replicating to different tables in the target database, you can set up a single target database connection and define the tables to replicate in the Replication wizard. If you are replicating from tables in multiple source databases to a single table in the target database, a restriction applies. Even though you are targeting a single database, connections to the same table cannot be shared so you will need to set up a target connection that matches each source connection using the same table.
The replication requires a unique target connection. You will also need to define replication details for each table involved in the replication. For example, if you have two tables on two source databases that both need to be replicated to the target database, you will need to set up two replications.
Replication details depend on whether you intend to perform a refresh replication, perform synchronization, or set up mirroring from the source to the target database.