How to load multiple relational databases with Invantive Data Replicator?

Wiki (en)
, , , ,
1

Using Invantive Data Replicator data from cloud applications and other supported data sources can be replicated as relational data in ANSI SQL-based databases: SQL Server, Oracle, PostgreSQL and MySQL.

Data Replicator performs metadata management by creating and altering tables and views, and also handles the staging/loading process such that a 24x7 availability can be achieved. For this purpose, besides the tables with replicated data a number of tables is used for internal housekeeping by Data Replicator. These are called the “Data Replicator repository”.

Larger environments often desire to load different data into multiple databases or the same data into multiple databases.

This is in general easy. This topic provides generic instructions on setting up loading multiple relational databases with Data Replicator.

There are some restrictions when using trickle loading, since the same feed of events must be processed into multiple repositories. There is currently no knowledge base article on setting up such an environment; please leave a clear question on the forums including details of the desired effect and setup when this occurs or acquire consulting.

Configuration Multiple Databases

Invantive Data Replicator is an additional driver which is associated with a (virtual) database. Databases are defined as settings*.xml files (settings.xml format explanation), typically in the folder %USERPROFILE%\invantive. More tips on the structure of settings*.xml can be found in Network - Invantive Data Hub or by searching for settings.xml on these forums.

A single repository set-up has a definition like:

<?xml version="1.0" encoding="utf-16"?>
<settings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="3" forcedefault="false">
  <group name="Data Replicator" >
    <connection 
     name="Replicator 1" 
     dataCacheConnectionString="Provider=SqlServer;connectionString={...}"
    >
      <database order="0" provider="Dummy" />
    </connection>
  </group>
</settings>

In this definition, the connection element defines a (virtual) database which in this case also uses Invantive Data Replicator by specifying the dataCacheConnectionString attribute.

The addition of another repository is quite simple. Just copy the connection element and change the name and dataCacheConnectionString attributes. The resulting XML in the settings*.xml file would be:

<?xml version="1.0" encoding="utf-16"?>
<settings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="3" forcedefault="false">
  <group name="Data Replicator" >
    <connection 
     name="Replicator 1" 
     dataCacheConnectionString="Provider=SqlServer;connectionString={...}"
    >
      <database order="0" provider="Dummy" />
    </connection>
    <connection 
     name="Replicator 2"
     dataCacheConnectionString="Provider=SqlServer;connectionString={...different...}"
    >
      <database order="0" provider="Dummy" />
    </connection>
  </group>
</settings>

That’s all.

Some remaining remarks:

  • The connection string to the relational SQL-database must point to another location, such as a different database on SQL Server.
  • Invantive Data Replicator allows at most one instance to connect to a Data Replicator repository. Therefore it maintains a list of active and historical sessions in the table dc_sessions. Crashed sessions are automatically removed after one day or directly when they ran on the same node.
  • Be careful when running multiple versions of Data Replicator: when connecting by accident with a newer version to a repository that should only be used by an old version, the repository is automatically and permanently upgraded.