Multi-Path Replication from Adaptive Server to HANA DB

Set up a Multi-Path Replication system with two primary and replicate paths for end-to-end replication from an Adaptive Server primary to a HANA DB replicate.

Prerequisites

This scenario assumes you have already installed and configured:

a primary Adaptive Server database and RepAgent thread
Replication Server
ExpressConnect for HANA DB
a replicate HANA DB database

Task

At the primary Adaptive Server database, select or create two sets of tables or stored procedures that you want to replicate through two replication paths. These transaction sets must be divisible into parallel replication paths.
Create a default connection to the primary Adaptive Server database.

Use create connection or rs_init to create the default connection. See Replication Server Configuration Guide > Configure Replication Server and Add Databases using rs_init.
Enable Multithreaded Adaptive Server RepAgent and Multiple Paths for Adaptive Server RepAgent.
You can also set the memory and send buffers available to Adaptive Server RepAgent. See Replication Server Administration Guide Volume 2 > Performance Tuning > Multi-Path Replication > Multiple Primary Replication Paths > Enabling Multithreaded RepAgent and Multiple Paths for RepAgent.
1. Enable multithreaded RepAgent.
  At the primary Adaptive Server, enter:
```
sp_config_rep_agent pdb, ‘multithread rep agent’, ‘true’
```
  where pdb is the primary Adaptive Server database.
2. Set the number of replication paths for RepAgent.
  For example, to enable two paths, enter:
```
sp_config_rep_agent pdb, 'max number of replication paths', '2'
```
Create an alternate replication path from the primary database.
See Replication Server > Administration Guide: Volume 2 > Performance Tuning > Multi-Path Replication > Multiple Primary Replication Paths > Creating Multiple Primary Replication Paths.
1. Create an alternate replication path named pdb_conn2 between the primary database and Replication Server.
  At the primary database, enter:
```
sp_replication_path "pdb", 'add',
"pdb_conn2", "PRS",
"muser", "mpwd"
```
  where:
  - PRS is the Replication Server.
  - muser is the maintenance user.
  - mpwd is the maintenance user password.
  (Optionally) Create logical paths that you can use to distribute data and objects bound to a physical path to multiple Replication Servers. See Replication Server Administration Guide: Volume 2 > Performance Tuning > Multi-Path Replication > Multiple Primary Replication Paths > Creating Logical Primary Replication Paths .
2. Create the corresponding alternate primary connection in Replication Server, and use the same Adaptive Server data server name with the alternate path name—pdb_conn2.
  At the Replication Server, enter:
```
create alternate connection to pds.pdb
named pds.pdb_conn2
set error class to rs_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username to muser
set password to mpwd
with primary only
```
  where pds is the primary Adaptive Server.
The replication system contains two primary replication paths—the default and pdb_conn2.
Use admin show_connections, 'primary' to display the primary connections you have created.
At the replicate HANA DB instance, create a maintenance user for the replicate HANA DB instance:
```
CREATE USER muser PASSWORD mpwd;
```
Grant these authorities to the maintenance user:
- CREATE ANY
- DELETE
- DROP
- EXECUTE (on all replicate stored procedures)
- INDEX
- SELECT
- UPDATE (on all replicate tables)
If you are connecting to HANA DB with a standard connection and not using SAP Secure User Store, add an entry to your interfaces file identifying the replicate HANA DB data server, and restart Replication Server:
```
[dataservername]
master tcp ether hostname port 
query tcp ether hostname port
```
where hostname and port are the host and port number of the HANA DB dataserver, and dataservername is a label used to identify the host and port number.
If you are using SAP Secure User Store, create a user store of encrypted credentials:
```
hdbuserstore set rds myhost:xxxxx my_securestore_user my_securestore_pwd
```
where
- rds is the key for the secure store entry
- myhost:xxxxx is the connection environment host name and port number
- my_securestore_user and my_securestore_pwd are SAP Secure User Store credentials
Create a connection to the replicate HANA DB instance using ExpressConnect for HANA DB.
For a standard connection:
```
create connection to rds.rdb
using profile rs_ase_to_hanadb;ech
set username muser
set password mpwd
go
```
For SAP Secure User Store:
```
create connection to rds.rdb
using profile rs_ase_to_hanadb;ech
set username auser
set password apwd
set dsi_connector_sec_mech to "hdbuserstore"
go
```
where:
- rds is the replicate HANA DB data server. For a standard connection, this must match the interfaces file entry. For an SAP Secure User Store connection, this must match what you used as the key to create a user store of encrypted credentials with the hdbuserstore utility.
- rdb is placeholder: You must provide a value, but it is not used..
- muser is the maintenance user for the replicate HANA DB instance.
- mpwd is the replicate HANA DB maintenance user password.
- auser and apwd are unused values supplied only to satisfy the syntax of the create connection command.
Note: The secure store must be created with the same operating system user ID that starts and runs Replication Server. Otherwise, Replication Server cannot access the secure user store.

Create an alternate connection to the replicate HANA DB database through ExpressConnect for HANA DB.

For a standard connection:

create alternate connection to rds.conn2
using profile rs_ase_to_hanadb;ech
set username muser
set password mpwd
go

For SAP Secure User Store:

create connection to rds.conn2
using profile rs_ase_to_hanadb;ech
set username auser
set password apwd
set dsi_connector_sec_mech to "hdbuserstore"
go

At the primary database, mark the ptab1 and ptab2 tables for replication.
```
sp_setreptable ptab1,'true'
go
sp_setreptable ptab2,'true'
go
```
Set the distribution mode at the primary database to distribute by object binding:
```
sp_config_rep_agent pdb, ‘multipath distribution model’, 'object’
```
Quiesce Replication Server and restart RepAgent.
See Replication Server Administration Guide Volume 1 > Manage a Replication System > Quiesce Replication Server > Quiescing a Replication System.
Bind the ptab2 table to the pdb_conn2 alternate connection.
```
sp_replication_path pdb, 'bind', "table", "dbo.ptab2", "pdb_conn2"
```
You can only bind objects to alternate replication paths. All objects that you do not bind to an alternate replication path, such as the ptab1 table, use the default path instead.
Create two replication definitions against the primary Adaptive Server database:
For example to create the ptab1_repdef replication definition for the ptab1 table:
```
create replication definition ptab1_repdef
with primary at pds.pdb
with all tables named 'ptab1'
...
go
```
To create the ptab2_repdef replication definition for the ptab2 table:
```
create replication definition ptab2_repdef
with primary at pds.pdb
with all tables named 'ptab2'
...
go
```
Note: These replication definitions must use the default primary connection name.
If the primary connection and alternate primary connection are on different Replication Servers, create replication definitions on each Replication Server.
Create a subscription against the default replicate connection.
For example, to create the ptab1_sub subscription for the ptab1_repdef replication definition:
```
create subscription ptab1_sub 
for ptab1_repdef
with replicate at rds.rdb
without materialization
go
```
Create a subscription against the alternate replicate connection.
For example, to create the ptab2_sub subscription for the ptab2_repdef replication definition:
```
create subscription ptab2_sub 
for ptab2_repdef
with replicate at rds.conn2
without materialization
go
```