Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
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 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.