View Full Version : Replicated from different sources to consolidated target

Arjen Visser
06-20-2012, 10:28 PM
I want to run a scenario by you and see if you have any comments, best practices, or advise on how to best set it up and if it can be done adequately with dbvisit replicate. We have several source databases with different schemas that we would like to replicate to one consolidated "data store" database. This data store database could have one schema with tables from all of the different sources, or it could have several schemas each from a different source. In any case, we would need a mine process running on each source, and many apply processes running that each apply data from a source. The tables would be unique to each mine database and would not be written to by more than one apply process.

Does this sound like something that would be a good use for dbvisit replicate? Any suggestions on how best to set up several apply processes on the apply side? Any other best practices recommendations ?


This is woud be a good choice to use Dbvisit Replicate in this scenario.
It does not matter for Dbvisit Replicate if the target tables are in a single schema or are in separate schemas. If they are in a single schema they do have to be unique.

As you said, each replication would have a MINE and APPLY process, and you would have many apply processes on the consolidated target.
Recommendations and tips:

Each MINE and APPLY process should be installed as a separate unique dbvrep user. So for the first pair, install as dbvrep, then dbvrep2, dbvrep3 etc. This is so that in the consolidated target database each replication is distinct and can be controlled separately.
Each apply process takes about 750M to 1G of memory, so ensure you have enough memory for all the apply processes that you want to run.
If the data store is going to have all the replicated tables in one schema, then use the schema rename to point the target tables all to the same schema.
Each apply process has to have a unique name to be able to run multiple apply proceses on the same server. So you can name them APPLY, APPLY2, APPLY3 etc. When setting up one-way replication in the setup wizard, it is currently not possible to add a suffix to the apply process to rename to APPLY2, but once the setup wizard has generated the scripts, it is easy to edit them and change APPLY to APPLY2 etc, before running the *-all.sh script.