Skip to main content

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 on the user-name menu at the top right corner of the web UI.

bidirectional_403.png

In this release, bidirectional replication is supported for Oracle, MariaDB, 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.

Note

When doing bidirectional replication:

  • Schema evolution is not supported.

  • MS SQL Reader's Support Transaction property must be True.

  • Oracle Reader's Committed Transaction property must be True.

Please Contact Striim support to determine whether your databases are compatible with bidirectional replication.

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 MariaDB, MySQL, or PostgreSQL:

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

for Oracle (table name must be uppercase):

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));