Skip to Content
Technical Articles
Author's profile photo Fatimah Areola

Developing Scripts for SAP HANA Cloud Adaptive Server Enterprise Replication Server

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

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.” To learn more about how to provision ASE and replication server instances, visit the Onboarding Guide.

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

The SQL endpoint for an instance can be found in SAP HANA Cockpit.

 

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’

Next Steps

At this point, we’ve walked through setting up and connecting to ASE and replication server instances, as well as writing a script to set up a database replication. We’ve tested our script, and developed a secondary script to reverse our database replication. Hopefully, you are now able to test SAP HANA Cloud Adaptive Server Enterprise and SAP HANA Cloud Adaptive Server Enterprise replication server in your organization.

To learn more about SAP HANA Cloud ASE and ASE replication server, you can follow the tags “SAP HANA Cloud Services” and “SAP HANA Cloud.” You can also visit the SAP HANA learning journey for SAP HANA Cloud ASE.

Assigned tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.