SAP HANA Cloud Replication Scripts: Efficient Development Guide

Throughout this post, we will walk through an example script for setting up an SAP HANA Cloud Adaptive Server Enterprise replication server.

SAP Sales Cloud Certification Preparation Guide

This blog post will walk through the following actions:

Prerequisites:

Provisioning SAP HANA Cloud Adaptive Server Enterprise and replication server instances.

Getting Started:

Setting up your Interfaces File.

Creating your Replication Server script:

Setting instance details within your SAP HANA Cloud ASE replication server script.
Defining interfaces for each server (the host name and connectivity information).
At the replication server, creating connections to each SAP HANA Cloud ASE server.
At the primary SAP HANA Cloud ASE server, setting up the replication agent.
At the SAP HANA Cloud ASE replication server, creates a database replication definition and subscription.

Testing your Script:

Creating a small table of 6 rows, and confirming that rows have been replicated.
Creating a table of 100,00 rows and querying the progress of replication.

Teardown:

Restoring your SAP HANA Cloud ASE and ASE replication servers to the state they were before you started.

Pre-requisites

There are a few pre-requisites to being able to run these scripts. Users will need to have existing access to SAP HANA Cloud, and be working on a Linux machine with the Client SDK installed.

In order to run the demo scripts, customers will need to create two ASE instances and one ASE replication server instance. The Connections option for all instances should be set to “Allow all IP addresses.”

Take note of the name and SQL endpoint for each instance. This information can be found by visiting SAP HANA Cockpit.

Developing Scripts for SAP HANA Cloud Adaptive Server Enterprise Replication Server

Getting Started

1. You will need an interfaces file to connect to your instances. Details for both SAP HANA ASE instances and the ASE replication server instance should all be included in the file. The format for your interfaces file will likely be as follows.

[INSTANCE NAME]
        master tcp ether [SQL ENDPOINT] [PORT] ssl="CN=hanacloud.ondemand.com"
        query tcp ether [SQL ENDPOINT] [PORT]  ssl="CN=hanacloud.ondemand.com"

Here’s an example from our instances file:

Test_ASE_3_25
        master tcp ether 916d3701-7e05-48f5-be1c-dfee64f24c75.ase.hxtp.prod-us21.hanacloud.ondemand.com 443 ssl="CN=hanacloud.ondemand.com"
        query tcp ether 916d3701-7e05-48f5-be1c-dfee64f24c75.ase.hxtp.prod-us21.hanacloud.ondemand.com 443  ssl="CN=hanacloud.ondemand.com"

Test_SRS_3_25
         master tcp ether d34e568c-4dde-43c3-90fe-587cd2858c7c.srs.hxtp.prod-us21.hanacloud.ondemand.com 443 ssl="CN=hanacloud.ondemand.com"
        query tcp ether d34e568c-4dde-43c3-90fe-587cd2858c7c.srs.hxtp.prod-us21.hanacloud.ondemand.com 443  ssl="CN=hanacloud.ondemand.com"

Test_ASE2_3_31_21
        master tcp ether c4a4b627-0c7f-4521-9d4b-c61e8cce9a88.ase.hxtp.prod-us21.hanacloud.ondemand.com 443 ssl="CN=hanacloud.ondemand.com"
        query tcp ether c4a4b627-0c7f-4521-9d4b-c61e8cce9a88.ase.hxtp.prod-us21.hanacloud.ondemand.com 443  ssl="CN=hanacloud.ondemand.com"

Creating your Replication Server Script:

Your script will require credentials from your SAP HANA Cloud ASE and ASE replication server instances, specifically the name of each instance and the aseadmin or repadmin passwords.

In our example, we’ve used variables to refer to the SAP HANA Cloud ASE instance names, passwords, and host addresses:

# ------------------------------------------------------------------------------
# Set variables
# ------------------------------------------------------------------------------
# ASE_1 should be the name of the primary ASE server as listed in your interfaces file
# For example:
# ASE_1=DEMO_ASE_1
ASE_1=
# ASE_2 should be the name of the secondary ASE server as listed in your interfaces file
ASE_2=
# ASE_R should be the name of the Replication server as listed in your interfaces file
ASE_R=
# If you use the same password for each system, you just need to set it once here.
# For example:
# PASSWORD=MyWeakPassword
ASE_1_PASSWORD=$PASSWORD
ASE_2_PASSWORD=$PASSWORD
ASE_R_PASSWORD=$PASSWORD
# Take the host addresses from the SQL endpoints for the ASE_1 and ASE_2 server, dropping
# the port. 
# For example, this may look like this:
# ASE_1_HOST=f376c6c8-cbec-427c-9278-2ab7c941f6c8.ase.hxtp.beta-us21.hanacloud.ondemand.com
ASE_1_HOST=
ASE_2_HOST=

The first step is to use this information saved above to define interface entries for each ASE instance.

# echo
# echo
# echo At $ASE_R, create interface entries for $ASE_1 and $ASE_2...
# echo ==========================================================================================
# echo
# echo
isql -U repadmin -P $ASE_R_PASSWORD -J utf8 -S $ASE_R -e -w1000 <<EOF
sysadmin interface, insert, $ASE_1, "$ASE_1_HOST", 443, 'ssl="CN=hanacloud.ondemand.com"'
go
EOF

isql -U repadmin -P $ASE_R_PASSWORD -J utf8 -S $ASE_R -e -w1000 <<EOF
sysadmin interface, insert, $ASE_2, "$ASE_2_HOST", 443, 'ssl="CN=hanacloud.ondemand.com"'
go
EOF

# isql -U repadmin -P $ASE_R_PASSWORD -J utf8 -S $ASE_R -e -w1000 <<EOF
# sysadmin interface, show
# go
# EOF

Next, we create connections to each ASE server, and the target database from the ASE replication server.

In our example, this is the sales database.

# echo
# echo
# echo At $ASE_R, create connections to $ASE_1 and $ASE_2...
# echo ==========================================================================================
# echo
# echo
isql -U repadmin -P $ASE_R_PASSWORD -J utf8 -S $ASE_R -e -w1000 <<EOF
create connection to $ASE_1.sales
set error class to rs_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username to sales_maint 
set password to $ASE_R_PASSWORD
set stream_replication to 'true'
with log transfer on
use login aseadmin password '$ASE_1_PASSWORD'
set ra_user rauser 
set password $ASE_R_PASSWORD
go
EOF
echo 
echo 
sleep 2 

isql -U repadmin -P $ASE_R_PASSWORD -J utf8 -S $ASE_R -e -w1000 <<EOF
create connection to $ASE_2.sales
set error class to rs_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username to sales_maint 
set password to $ASE_R_PASSWORD
set stream_replication to 'true'
with log transfer on
use login aseadmin password '$ASE_2_PASSWORD'
set ra_user rauser 
set password $ASE_R_PASSWORD
go
EOF

# isql -U repadmin -P $ASE_R_PASSWORD -J utf8 -S $ASE_R -e -w1000 <<EOF
# admin show_connections
# go
# EOF

Then, set up the replication agent in the primary ASE server.

# echo
# echo
# echo At $ASE_1, set up the replication agent...
# echo ==========================================================================================
# echo
# echo
isql -U aseadmin -P $ASE_R_PASSWORD -J utf8 -S $ASE_1 -e -w1000 <<EOF
use sales
go
exec sp_reptostandby sales, 'all'
go
exec sp_config_rep_agent sales, 'send warm standby xacts', 'true'
go
exec sp_stop_rep_agent sales
go
waitfor delay "00:00:03"
go
exec sp_start_rep_agent sales
go
EOF

Finally, we can create a database replication definition and subscription.

# echo
# echo
# echo At $ASE_R, create database replication definition and subscription...
# echo ==========================================================================================
# echo
# echo
isql -U repadmin -P $ASE_R_PASSWORD -J utf8 -S $ASE_R -e -w1000 <<EOF
create database replication definition salesrep 
with primary at $ASE_1.sales
replicate DDL
go
create subscription salessub 
for database replication definition salesrep 
with primary at $ASE_1.sales
with replicate at $ASE_2.sales
without materialization
go
# check subscription salessub 
# for database replication definition salesrep 
# with primary at $ASE_1.sales
# with replicate at $ASE_2.sales
# go
EOF

Testing your Script

You may wish to test your script as well. In the following section we will walk through creating two tables, in our primary ASE server and confirming that they have been replicated within our secondary ASE server.

First, we create a small table testable in our primary ASE instance.

# echo
# echo
# echo At $ASE_1, create a table testtable...
# echo ==========================================================================================
# echo
# echo
isql -U aseadmin -P $ASE_1_PASSWORD -J utf8 -S $ASE_1 -e -w1000 <<EOF
use sales
go
create table testtable (
mypkey int primary key, 
col1 char(50) not null, 
col2 int null, col3 varchar(30) 
null)
go
grant all on testtable to public
go
insert into testtable values (1, "hello", null, "world")
insert into testtable values (2, "hello", 1, "world")
insert into testtable values (3, "hello", 2, null)
insert into testtable values (4, "hello", null, null)
insert into testtable values (5, "world", 3, "hello")
insert into testtable values (6, "hello", null, "hello")
select * from testtable
go
EOF

Now, we can test our secondary ASE instance to see if the testable rows have moved.

# echo
# echo
# echo At $ASE_2, see if the testtable rows have moved...
# echo ==========================================================================================
# echo
# echo
isql -U aseadmin -P $ASE_2_PASSWORD -J utf8 -S $ASE_2 -e -w1000 <<EOF
use sales
go
select * from testtable
go
EOF

The results after you run your script should look similar to this:

We can also query the progress of replication. To do this, we will use a larger table named PurchaseOrder and load it with 100,000 rows.

# echo
# echo
# echo At $ASE_1, Create the PurchaseOrder table and load data...
# echo ==========================================================================================
# echo
# echo
isql -U aseadmin -P $ASE_1_PASSWORD -J utf8 -S $ASE_1 -e -w1000 <<EOF
use sales
go
CREATE TABLE [PurchaseOrder] (
      [PurchaseOrderId] NVARCHAR(10) NOT NULL 
    , [CreatedBy] NVARCHAR(10) NOT NULL 
    , [CreatedAt] DATE NOT NULL 
    , [ChangedBy] NVARCHAR(10)
    , [ChangedAt] DATE 
    , [NoteId] NVARCHAR(10) DEFAULT '' NOT NULL
    , [PartnerId] NVARCHAR(10)
    , [Currency] NVARCHAR(5) NOT NULL 
    , [GrossAmount] DECIMAL(15 ,2) DEFAULT 0 NOT NULL 
    , [NetAmount] DECIMAL(15 ,2) DEFAULT 0 NOT NULL 
    , [TaxAmount] DECIMAL(15 ,2) DEFAULT 0 NOT NULL 
    , [LifecycleStatus] NVARCHAR(1)
    , [ApprovalStatus] NVARCHAR(1)
    , [ConfirmStatus] NVARCHAR(1)
    , [OrderingStatus] NVARCHAR(1)
    , [InvoicingStatus] NVARCHAR(1)
    , PRIMARY KEY ([PurchaseOrderId]))
go
grant all on PurchaseOrder to public
go
EOF

bcp sales..PurchaseOrder in PurchaseOrder.csv -Y -b 10000 -S $ASE_1 -U aseadmin -P $ASE_1_PASSWORD -f bcp.fmt

In our example, we used a bcp.fmt file to help set up this table:

bcp.fmt

10.0
16
1	SYBCHAR	1	30	"\","	1	PurchaseOrderId
2	SYBCHAR	1	30	"\","	2	CreatedBy
3	SYBCHAR	1	30	"\","	3	CreatedAt
4	SYBCHAR	1	30	"\","	4	ChangedBy
5	SYBCHAR	1	30	"\","	5	ChangedAt
6	SYBCHAR	0	30	","	    6	NoteId
7	SYBCHAR	1	30	"\","	7	PartnerId
8	SYBCHAR	1	15	"\","	8	Currency
9	SYBCHAR	1	30	"\","	9	GrossAmount
10	SYBCHAR	1	30	"\","	10	NetAmount
11	SYBCHAR	1	30	"\","	11	TaxAmount
12	SYBCHAR	1	3	"\","	12	LifecycleStatus
13	SYBCHAR	1	3	"\","	13	ApprovalStatus
14	SYBCHAR	1	3	"\","	14	ConfirmStatus
15	SYBCHAR	1	3	"\","	15	OrderingStatus
16	SYBCHAR	1	3	"\"\n"	16	InvoicingStatus

Finally, we can write a loop to query the status of the replication while it is in progress.

while true
do
echo
echo
echo At $ASE_2, see if the PurchaseOrder rows have moved...
echo ==========================================================================================
echo
read -n 1 -s -r -p "Query PurchaseOrder in $ASE_2? [y]/n " continue
continue=${continue:-y}
if [ "$continue" = "N" ] || [ $continue = "n" ]; then
   echo
   echo -e "\tYou pressed $continue. Exiting..."
   echo
   exit
fi
   # clear 
   # echo
    # 
   # echo At $ASE_2, see if the PurchaseOrder rows have moved...
   # echo ==========================================================================================
   # echo
   # echo
   isql -U aseadmin -P $ASE_2_PASSWORD -J utf8 -S $ASE_2 -e -w1000 <<EOF
use sales
go
select count(*) as [PurchaseOrderCount] from PurchaseOrder
go
EOF
done

The results should look similar to this:

Teardown

A separate script can be developed to undo any changes made in previous steps. The teardown script must include instance names and passwords just as with the replication script.

# ------------------------------------------------------------------------------
# Set variables
# ------------------------------------------------------------------------------
# ASE_1 should be the name of the primary ASE server as listed in your interfaces file
ASE_1=
# ASE_2 should be the name of the secondary ASE server as listed in your interfaces file
ASE_2=
# ASE_R should be the name of the Replication server as listed in your interfaces file
ASE_R=
PASSWORD=
ASE_1_PASSWORD=$PASSWORD
ASE_2_PASSWORD=$PASSWORD
ASE_R_PASSWORD=$PASSWORD

The teardown script should then drop the subscription, replication definition, connections, and interfaces from the replication server.

echo 
echo At $ASE_R, drop subscription...
echo
isql -U repadmin -P $ASE_R_PASSWORD -J utf8 -S $ASE_R -e -w 1000 <<EOF
drop subscription salessub 
for database replication definition salesrep 
with primary at $ASE_1.sales
with replicate at $ASE_2.sales
without purge
go
EOF

sleep 4

echo 
echo At $ASE_R, drop replication definition...
echo
isql -U repadmin -P $ASE_R_PASSWORD -J utf8 -S $ASE_R -e -w 1000 <<EOF
drop database replication definition salesrep 
with primary at $ASE_1.sales
go
EOF

sleep 2

echo 
echo At $ASE_R, drop connections and interfaces...
echo
isql -U repadmin -P $ASE_R_PASSWORD -J utf8 -S $ASE_R -e -w 1000 <<EOF
drop connection to $ASE_1.sales
go
drop connection to $ASE_2.sales
go
sysadmin interface, delete, $ASE_1
go
sysadmin interface, delete, $ASE_2
go
EOF

The output of running the teardown script should look like this:

Considerations

Users may run into an error that requires for them to increase the size of their default data cache. If this is the case, connect to your ASE instance and run the following command.

sp_cacheconfig ‘default data cache’, ‘512M’

Rating: 0 / 5 (0 votes)