Home / Repstance User Guide / CONFIGURE REPLICATION WITH REPSTANCE / Multi-Target Replication Configuration

Multi-Target Replication Configuration

Repstance allows to use multiple Target Databases to propagate data from single Source without necessity to "re-capture" it for each Target and as the result to avoid producing unnecessary load to the Source database. For each Target database the Apply Process to be configured that implements own set of rules that are to specify objects, transformations, filtering etc.

The example demonstrates how to setup replication of “dbo” and “report” schemas from the SQL Server database into another SQL Server for disaster recovery purposes, while in additional the "report" schema is to be replicated into the Snowflake database to be used for reporting needs.

Note - both schemas must be created in the Target SQL Server database and the only “report” in the Snowflake. If the schemas do not have any tables, the tables will be created automatically

Prepare Source and Target databases

Run the following command to prepare SQL Server database as a Source:

[http://localhost:8796/] repcli>prepare database=source dbtype=mssql server=10.20.30.1 port=1433 user=sa password=xxx dbname=MultiTarget housekeeping=1

Run the following command to prepare SQL Server database as a Target:

[http://localhost:8796/] repcli>prepare database=target dbtype=mssql server=10.20.30.2 port=1433 user=sa password=xxx dbname=MultiTarget

Run the following command to prepare Snowflake database as a Target:

[http://localhost:8796/] repcli>prepare database=target dbtype=snowflake account=ab12345 region=eu-west-2.aws warehouse=REPORT_WH dbname=REP user=admin password=xxx

Create Capture Process

Run the following command to create “MTCap1” Capture Process:

[http://localhost:8796/] repcli>prepare process=capture id=1 dbtype=mssql server=10.20.30.2 port=1433 user=sa password=xxx dbname=MultiTarget name=MTCap1 dmlinclude=dbo.%,report.% ddlinclude=dbo.%,report.% loadinclude=dbo.%:C,report.%:C autostart=1

Create Apply Processes

Both Apply processes to be configured to consume data from the “MTCap1” Capture Process.

Run the following command to create Apply Process for the Target SQL Server:

[http://localhost:8796/] repcli>prepare process=apply id=1 dbtype=mssql server=10.20.30.2 port=1433 user=sa password=xxx dbname=MultiTarget capturename=MTCap1 ddlprocessing=native autostart=1

Run the following command to create the Apply Process for Snowflake:

[http://localhost:8796/] repcli>prepare process=apply id=2 dbtype=snowflake account=ab12345 region=eu-west-2.aws warehouse=REPORT_WH dbname=REP user=admin password=xxx capturename=MTCap1 ddlprocessing=map map=1,rule=(dbo.%:NULL) autostart=1

The “map=1,rule=(dbo.%:NULL)” transformation rule is used to ignore any changes of “dbo” schema objects, so the only “report" schema data is to be replicated (see Objects Mapping and Possible Transforms for the details).

Initial Data Migration

BI-Direction