Setting Up Bidirectional Replication with SAP Replication Server

[former_member192572]

When you have multiple data centers located in different global locations, and want to synchronize data among them, you need to set up bidirectional replication between the data centers – that is, you want to make sure that data gets replicated from one data center to another and vice versa. In this blog, I’ll tell you how to set up bidirectional multisite availability (MSA) replication between SAP ASE servers, using SAP Replication Server (SRS).

Prerequisite

This blog assumes you’ve created db1 under ASE1 and db2 under ASE2, and that:

• The databases are newly created and empty and there’s no need for materialization.


• Data manipulation language (DML) replication from db1 to db2 and from db2 to db1 is already setup.


• The maintenance user is maint_user and has replication_role.

Now, we would like to set up the bidirectional replication for data definition language (DDL) between db1 and db2.

First, set up DDL replication from db1 to db2:

On ASE1:

• Mark db1 on ASE1.

use db1

go

sp_reptostandby db1,’all’

go

• Set the send warm standby xacts parameter to true. After setting it to true, Replication Agent thread for SAP ASE (RepAgent for short) sends maintenance user transactions, schema changes, and system transactions to db2.

use db1

go

sp_config_rep_agent db1, ‘send warm standby xacts’, ‘true’

go

• Stop RepAgent on ASE1:

sp_stop_rep_agent db1

go

• Start RepAgent on ASE1:

sp_start_rep_agent db1

go

 

On ASE2:

Grant set session authorization to the maintenance user maint_user. This ensures the users used to replicate DDLs have the right permissions.

use master

go

grant set session authorization to maint_user

go

Note: Before granting authorization, make sure the maintenance user maint_user is in the master database. If it isn’t, use the following commands to add it:

use master

go

sp_adduser maint_user

go

 

On RS1:

• Turn on the DDL replication function for db2. The dsi_replication_ddl parameter supports bidirectional DDL replication by determining whether transactions will be replicated to the primary database. When dsi_replication_ddl is set to on, DSI sends set replication off to the replicate database, which instructs it to mark any new DDL transactions in system log not to be replicated. Since these DDL transactions are not replicated back to the primary database, it enables DDL transaction replication in a bidirectional MSA replication environment.

alter connection to ASE2.db2 set dsi_replication_ddl ‘on’

go

• Suspend the connection:

suspend connection to ASE1.db1

go

• Resume the connection:

resume connection to ASE1.db1

go

• Create a database replication definition for db1:

create database replication definition repdef

with primary at ASE1.db1

replicate ddl

go

• Create a subscription for db1:

create subscription repsub for database replication definition repdef

with primary at ASE1.db1

with replicate at ASE2.db2

without materialization

go

 

Second, set up DDL replication from db2 to db1:

Repeat the above steps. Note that this time you need to exchange ASE1 and ASE2 with each other, and exchange db1 and db2 with each other when executing the commands.

 

After setting up DDL replication from db1 to db2 and from db2 to db1, bidirectional replication between the two databases is established. Enjoy!