Striim 3.9.7 documentation

Bidirectional replication

Bidirectional replication allows synchronization of two databases, with inserts, updates, and deletes in each replicated in the other. The columns in the replicated tables must have compatible data types. If your Striim cluster is licensed for bidirectional replication, this will be indicated below your user name at the top right corner of the web UI.

bidirectional.png

In this release, bidirectional replication is supported for Oracle, MariaDB Galera Cluster, MySQL, PostgreSQL, and SQL Server. It uses two data flows, one from a source in database A to a target in database B, the other the reverse.The following example application would perform bidirectional replication between MySQL and SQL Server:

CREATE APPLICATION BidirectionalDemo RECOVERY 1 SECOND INTERVAL;
CREATE SOURCE ReadFromMySQL USING MySQLReader (
 Username: 'striim',
  Password: '*******',
  ConnectionURL: 'mysql://192.0.2.0:3306',
  Tables: 'mydb.*',
  BidirectionalMarkerTable: 'mydb.mysqlmarker'
)
OUTPUT TO MySQLStream;

CREATE TARGET WriteToSQLServer USING DatabaseWriter (
  ConnectionURL:'jdbc:sqlserver://192.0.2.1:1433;databaseName=mydb',
  Username:'striim',
  PassWord:'********',
  Tables: 'mydb.*,dbo.*',
  CheckPointTable: 'mydb.CHKPOINT',
  BidirectionalMarkerTable: 'mydb.sqlservermarker'
)
INPUT FROM MySQLStream;

CREATE SOURCE ReadFromSQLServer USING MSSQLReader (
  ConnectionURL:'192.0.2.1:1433',
  DatabaseName: 'mydb',
  Username: 'striim',
  Password: '*******',
  Tables: 'dbo.*',
  BidirectionalMarkerTable: 'mydb.sqlservermarker'
)
OUTPUT TO SQLServerStream;

CREATE TARGET WriteToMySQL USING DatabaseWriter (
  Username:'striim',
  PassWord:'********',
  ConnectionURL: 'mysql://192.0.2.0:3306',
  Tables: 'dbo.*,mydb.*',
  CheckPointTable: 'mydb.CHKPOINT',
  BidirectionalMarkerTable: 'mydb.mysqlmarker'
)
INPUT FROM SQLServerStream;
END APPLICATION BidirectionalDemo;

Striim requires a "marker table" in each database. It uses the information recorded in this table to detect and discard events that would create an infinite loop. To create the table, use the following DDL:

for MySQL or PostgreSQL:

CREATE TABLE <name> 
(componentId varchar(100) PRIMARY KEY, lastupdatedtime timestamp(6));

for Oracle:

CREATE TABLE <name> 
(componentId varchar2(100) PRIMARY KEY, lastupdatedtime timestamp(6));

for SQL Server:

CREATE TABLE <name> 
(componentId varchar(100) PRIMARY KEY, lastupdatedtime datetime2(6));