Multi-Target Replication

Repstance can send the changes of one Source Database to several Target Databases. The Capture Process reads the changes once and writes them to the trail files. Every Target has its own Apply Process that reads the same files, with its own rules for objects, transformations and filters, and at its own speed. The load of the Source Database does not grow with the number of the Targets.

This example replicates the schemas dbo and report of a SQL Server database to another SQL Server database, for disaster recovery, and the schema report to Snowflake, for reporting. The steps are the ones of Basic Replication Configuration.

What you need

• The same as in Basic Replication Configuration: Repstance is running and the logins have the privileges that are described in Database Privileges. The Source Database is testDB on db-src.example.com, one Target is testDB on db-tgt.example.com and the other is the Snowflake database DWH (see Snowflake as Target).
• The schemas exist in the Targets: dbo and report in the SQL Server Target and report in Snowflake. Repstance creates the tables, here with the Initial Load, but it does not create schemas.

Prepare the databases

Prepare the SQL Server database as a Source, the other SQL Server database as a Target and the Snowflake database as a Target:

Create the Capture Process

The Capture Process MTCap1 replicates both schemas. A parameter that takes several masks lists them separated by a comma. loadinclude copies the data that exists and creates the tables that are missing in the Targets (see Initial Load):

Create the Apply Processes

Both Apply Processes read from the Capture Process MTCap1. The Apply Process 1 applies everything to the SQL Server Target. The DDL statements are replayed as they are, which is the default for SQL Server (ddlprocessing=native):

The Apply Process 2 applies only the schema report to Snowflake. The rule map=1,rule=(dbo.%:NULL) excludes all the tables of the schema dbo for this process (see Objects Mapping and Possible Transforms). ddlprocessing=map makes the rule apply to the DDL statements too and rebuilds them for Snowflake:

How the Apply Processes share the trail files

The Capture Process writes the trail files once, and every Apply Process reads them at its own pace and keeps its own position in them. An Apply Process deletes a trail file when it has applied it, or later if cleanuptrail asks for that (see Prepare Apply Process), but it never deletes a file that another Apply Process of the same Capture Process still needs. An Apply Process that is stopped, or slow, therefore keeps the trail files on the disk of the Repstance server until it has caught up (see Stop any Capture or Apply Processes).

Check the replication

The command status shows the Capture Process and both Apply Processes, each with its own position and lag (see Status of any Capture or Apply Processes). To check that the data is the same in a Target, compare it with the Source: DATA COMPARISON.