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).
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:
- 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
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
- 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!