Take note of the name and SQL endpoint for each instance. This information can be found by visiting SAP HANA Cockpit.
[INSTANCE NAME]
master tcp ether [SQL ENDPOINT] [PORT] ssl="CN=hanacloud.ondemand.com"
query tcp ether [SQL ENDPOINT] [PORT] ssl="CN=hanacloud.ondemand.com"
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"
# ------------------------------------------------------------------------------
# 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=
# 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
# 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
# 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
# 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
# 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
# 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
# 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
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
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
# ------------------------------------------------------------------------------
# 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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
19 | |
14 | |
11 | |
10 | |
9 | |
8 | |
7 | |
7 | |
7 | |
5 |