- SAP Community
- Products and Technology
- Technology
- Technology Blogs by SAP
- Adding a DR node to ASE Always-on for a Custom App...
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.
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
c_baker
Employee
Options
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
12-09-2022
9:25 PM
(...continuing from Part 1)
Existing databases can be added to the HADR cluster for replication in one of 2 ways:
The first method has already been demonstrated in Part 1 using the response file to add the <SID> database to the HADR cluster and DR node.
The steps to follow when adding a database are as follows:
For example:
For step 1) and 2) above, the following commands are run on each instance - PRIMARY_ASE, COMPANION_ASE, DR_ASE - using isql:
with example output:
The initial creation of the HADR cluster (primary and companion) also created the DR_admin and DR_maint ASE logins. Adding the DR node also creates those logins. All have the same SUID and it is for this reason that the companion and DR ASE instances should be freshly created.
We will now add a user that will eventually own the database by adding it to the primary (active) node only:
Since the master database is replicated to the other nodes, this will be replicated as well, however, specific db_options, ownership changes and alias commands must be issued on each node. The following commands will therefore be issued on the ASE instance for each node (primary, companion and DR):
Successful execution of the commands can be checked by running
The database is now present on all 3 nodes, but not replicating and is empty on the companion and DR node (and the primary in this case).
Now the database is added to the HADR environment by issuing commands to the Primary RMA (not the ASE).
Add database to HADR environment for replication:
As some commands can run asynchronously, use 'sap_status task' to check that all tasks are complete before continuing:
'sap_status path' now shows replication paths active from the primary (Toronto) to the secondary (London) and the DR (Offsite):
Note that although the 'State' from the active (Toronto) to the DR (Offsite) shows as 'Active' it has not been materialized. The state from the active to the standby is also only 'Defined' and must also be materialized.
As there are separate paths to the companion and DR, materialization is performed for each.
Materialize database to Companion:
Materialize database to DR:
'sap_status_path' now shows:
All paths from active to standby and DR are now in 'Active' status.
SQL DML and DDL statements are now being replicated, this can be done by connecting an application to the primary (active) database and performing operations. The application used here is beyond the scope of this blog, but any application can be used.
In this case, a Java JDBC application using SAP jConnect 16.0 is used and the application is not HA-aware. No additional connection properties are added to the base URL: "jdbc:sybase:Tds:primarynode.watpm.c.na-ca-1.cloud.sap:5000"
All DDL statements executed by the application to create tables, indexes, etc, are replicated to both the standby and the DR. This can be demonstrated simply by running a command such as the following on each ASE instance (PRIMARY_ASE, COMPANION_ASE, DR_ASE):
Similarly, for DML, after loading data using batch inserts (java.sql.PreparedStatement.addBatch(), java.sql.PreparedStatement.executeBatch()) and then running the application to perform data changes (java.sql.PreparedStatement.executeUpdate()), the row counts for each table should match in each instance. e.g.
To perform a planned failover, 4 steps must take place with commands issued to the primary or companion RMA:
'sap_status path' should be issued between each command to ensure all tasks have been completed before proceeding to the next step.
1. Start the failover:
2. Drain any remaining transactions to DR:
Replication from the now active (companion) node back to the stanby (primary) and to the DR shows as suspended:
Two more commands are issued to mark those hosts available to receive changes from the now active (companion) node:
3/4. Make nodes available to accept transactions:
As the standby node (primary) is available for network connections, any attempt to login with a non-sa_role will be redirected to the active (companion) node. Running the previous application with no changes redirects the login to the active (companion) node automatically. Again, this can be confirmed by running comparison queries on each node to check the data. A simple example:
Similar to planned failover, performing a planned failback also takes 4 steps using commands issued to the primary or companion RMA:
Again, 'sap_status task' should be issued between each command to ensure all tasks have been completed before proceeding to the next step.
1. Start the failback:
2. Drain any remaining transactions to DR:
3/4. Make nodes available to accept transactions:
Running the application again with no changes (i.e. against the now-active primary node) yields the same row counts:
Connecting to an RMA instance on any node shows that the active instance is now, again, the ASE instance on the primary node:
(continued in Part 3…)
Adding a database to the HADR cluster
Existing databases can be added to the HADR cluster for replication in one of 2 ways:
- Using the setuphadr response file to create the devices and database.
- Using the RMA command 'sap_update_replication' and 'sap_materialize' commands.
The first method has already been demonstrated in Part 1 using the response file to add the <SID> database to the HADR cluster and DR node.
The steps to follow when adding a database are as follows:
- a database first must be created or exist on the primary (active) node.
- the database must be created on the standby (and DR nodes) and should be the same size as the primary.
- as the master database is already being replicated, any addition of a new SUID for dbo will be replicated, but the changes to the dbo for the database must be run on each target database instance as well.
- DR_maint (already an SUID in master) must also be added as an alias for the database's dbo per target instance. This is not done by .sap_update_replication' (below).
- any changes to a database's options must also be run per target instance.
- the database is added to the standby and dr nodes by running the 'sap_update_replication' RMA command.
- the database must be materialized to the target instances by issuing separate 'sap_materialize' RMA commands per target instance.
For example:
For step 1) and 2) above, the following commands are run on each instance - PRIMARY_ASE, COMPANION_ASE, DR_ASE - using isql:
disk init name="tpccdata",physname="/data/ASE/data/tpccdata.dat",size="2048M"
go
disk init name="tpcclog",physname="/data/ASE/data/tpcclog.dat",size="2048M"
go
create database tpcc on tpccdata = "2048M" log on tpcclog = "2048M"
go
sp_dboption tpcc, 'trunc. log on chkpt.', true
gowith example output:
PRIMARY_ASE:
CREATE DATABASE: allocating 131072 logical pages (2048.0 megabytes) on disk 'tpccdata' (131072 logical pages requested).
CREATE DATABASE: allocating 131072 logical pages (2048.0 megabytes) on disk 'tpcclog' (131072 logical pages requested).
Processed 103 allocation unit(s) out of 1024 units (allocation page 143104). 10% completed.
Processed 205 allocation unit(s) out of 1024 units (allocation page 156160). 20% completed.
Processed 308 allocation unit(s) out of 1024 units (allocation page 169216). 30% completed.
Processed 410 allocation unit(s) out of 1024 units (allocation page 182272). 40% completed.
Processed 512 allocation unit(s) out of 1024 units (allocation page 66560). 50% completed.
Processed 615 allocation unit(s) out of 1024 units (allocation page 208384). 60% completed.
Processed 717 allocation unit(s) out of 1024 units (allocation page 92928). 70% completed.
Processed 820 allocation unit(s) out of 1024 units (allocation page 234240). 80% completed.
Processed 922 allocation unit(s) out of 1024 units (allocation page 247296). 90% completed.
Processed 1024 allocation unit(s) out of 1024 units (allocation page 261888). 100% completed.
Database 'tpcc' is now online.
Database option 'trunc. log on chkpt.' turned ON for database 'tpcc'.
Running CHECKPOINT on database 'tpcc' for option 'trunc. log on chkpt.' to take effect.
(return status = 0)
COMPANION_ASE:
CREATE DATABASE: allocating 131072 logical pages (2048.0 megabytes) on disk 'tpccdata' (131072 logical pages requested).
CREATE DATABASE: allocating 131072 logical pages (2048.0 megabytes) on disk 'tpcclog' (131072 logical pages requested).
Processed 103 allocation unit(s) out of 1024 units (allocation page 143104). 10% completed.
Processed 205 allocation unit(s) out of 1024 units (allocation page 156160). 20% completed.
Processed 308 allocation unit(s) out of 1024 units (allocation page 40192). 30% completed.
Processed 410 allocation unit(s) out of 1024 units (allocation page 53248). 40% completed.
Processed 512 allocation unit(s) out of 1024 units (allocation page 66304). 50% completed.
Processed 615 allocation unit(s) out of 1024 units (allocation page 208640). 60% completed.
Processed 717 allocation unit(s) out of 1024 units (allocation page 221696). 70% completed.
Processed 820 allocation unit(s) out of 1024 units (allocation page 105728). 80% completed.
Processed 922 allocation unit(s) out of 1024 units (allocation page 118784). 90% completed.
Processed 1024 allocation unit(s) out of 1024 units (allocation page 261888). 100% completed.
Database 'tpcc' is now online.
Database option 'trunc. log on chkpt.' turned ON for database 'tpcc'.
Running CHECKPOINT on database 'tpcc' for option 'trunc. log on chkpt.' to take effect.
(return status = 0)
DR_ASE:
CREATE DATABASE: allocating 131072 logical pages (2048.0 megabytes) on disk 'tpccdata' (131072 logical pages requested).
CREATE DATABASE: allocating 131072 logical pages (2048.0 megabytes) on disk 'tpcclog' (131072 logical pages requested).
Processed 103 allocation unit(s) out of 1024 units (allocation page 13824). 10% completed.
Processed 205 allocation unit(s) out of 1024 units (allocation page 26880). 20% completed.
Processed 308 allocation unit(s) out of 1024 units (allocation page 169472). 30% completed.
Processed 410 allocation unit(s) out of 1024 units (allocation page 182528). 40% completed.
Processed 512 allocation unit(s) out of 1024 units (allocation page 195584). 50% completed.
Processed 615 allocation unit(s) out of 1024 units (allocation page 79360). 60% completed.
Processed 717 allocation unit(s) out of 1024 units (allocation page 92416). 70% completed.
Processed 820 allocation unit(s) out of 1024 units (allocation page 235008). 80% completed.
Processed 922 allocation unit(s) out of 1024 units (allocation page 248064). 90% completed.
Processed 1024 allocation unit(s) out of 1024 units (allocation page 261888). 100% completed.
Database 'tpcc' is now online.
Database option 'trunc. log on chkpt.' turned ON for database 'tpcc'.
Running CHECKPOINT on database 'tpcc' for option 'trunc. log on chkpt.' to take effect.
(return status = 0)
The initial creation of the HADR cluster (primary and companion) also created the DR_admin and DR_maint ASE logins. Adding the DR node also creates those logins. All have the same SUID and it is for this reason that the companion and DR ASE instances should be freshly created.
We will now add a user that will eventually own the database by adding it to the primary (active) node only:
create login tpcc with password "<password>" default database tpcc
goSince the master database is replicated to the other nodes, this will be replicated as well, however, specific db_options, ownership changes and alias commands must be issued on each node. The following commands will therefore be issued on the ASE instance for each node (primary, companion and DR):
use tpcc
go
sp_changedbowner tpcc, true
go
sp_addalias DR_maint, dbo
goSuccessful execution of the commands can be checked by running
use tpcc
go
sp_helpuser dbo
go
Users_name ID_in_db Group_name Login_name
---------- -------- ---------- ----------
dbo 1 public tpcc
(1 row affected)
Users aliased to user.
Login_name
----------
DR_maint
(return status = 0)
The database is now present on all 3 nodes, but not replicating and is empty on the companion and DR node (and the primary in this case).
Now the database is added to the HADR environment by issuing commands to the Primary RMA (not the ASE).
Add database to HADR environment for replication:
isql -UDR_admin -P<password> -Sprimarynode:7001 -w999
1> sap_update_replication add_db, tpcc
2> go
TASKNAME TYPE VALUE
-------------------------------------- --------------------- -------------------------------------------------------------------------------------------------
Update Replication Start Time Thu Dec 01 19:32:39 UTC 2022
Update Replication Elapsed Time 00:00:02
DRExecutorImpl Task Name Update Replication
DRExecutorImpl Task State Running
DRExecutorImpl Short Description Update configuration for a currently replicating site.
DRExecutorImpl Long Description Started task 'Update Replication' asynchronously.
DRExecutorImpl Additional Info Please execute command 'sap_status task' to determine when task 'Update Replication' is complete.
UpdateReplication Task Name Update Replication
UpdateReplication Task State Running
UpdateReplication Short Description Update configuration for a currently replicating site.
UpdateReplication Long Description Add database 'tpcc' into Replication Servers.
UpdateReplication Current Task Number 1
UpdateReplication Total Number of Tasks 4
UpdateReplication Task Start Thu Dec 01 19:32:39 UTC 2022
UpdateReplication Hostname primarynode.openstack.na-ca-1.cloud.sap
AddASEDatabaseForDisasterRecovery Task Name Add Database to Replication
AddASEDatabaseForDisasterRecovery Task State Running
AddASEDatabaseForDisasterRecovery Short Description Add an ASE database to the Replication System for Disaster Recovery support.
AddASEDatabaseForDisasterRecovery Long Description Adding database 'tpcc' to the replication server on host 'primarynode'.
AddASEDatabaseForDisasterRecovery Current Task Number 0
AddASEDatabaseForDisasterRecovery Total Number of Tasks 20
AddASEDatabaseForDisasterRecovery Hostname primarynode.openstack.na-ca-1.cloud.sap
AddLocalASEDatabaseForDisasterRecovery Task Name Add Local Database to Replication
AddLocalASEDatabaseForDisasterRecovery Task State Running
AddLocalASEDatabaseForDisasterRecovery Short Description Add an ASE database to the local Replication System for Disaster Recovery support.
AddLocalASEDatabaseForDisasterRecovery Long Description Adding database 'tpcc' to the replication server on host 'primarynode'.
AddLocalASEDatabaseForDisasterRecovery Hostname primarynode.openstack.na-ca-1.cloud.sap
ResumeDSI Task Name Resume DSI Connection
ResumeDSI Task State Running
ResumeDSI Short Description Resume the Replication Server connection to a database.
ResumeDSI Long Description Waiting 1 seconds: Waiting 10 seconds before checking if the command executed successfully.
ResumeDSI Hostname primarynode.openstack.na-ca-1.cloud.sap
(32 rows affected)
1>
As some commands can run asynchronously, use 'sap_status task' to check that all tasks are complete before continuing:
1> sap_status task
2> go
TASKNAME TYPE VALUE
----------------- --------------------- -------------------------------------------------------------------------
Status Start Time Thu Dec 01 19:32:39 UTC 2022
Status Elapsed Time 00:01:38
UpdateReplication Task Name Update Replication
UpdateReplication Task State Completed
UpdateReplication Short Description Update configuration for a currently replicating site.
UpdateReplication Long Description Update replication request to add database 'tpcc' completed successfully.
UpdateReplication Current Task Number 4
UpdateReplication Total Number of Tasks 4
UpdateReplication Task Start Thu Dec 01 19:32:39 UTC 2022
UpdateReplication Task End Thu Dec 01 19:34:17 UTC 2022
UpdateReplication Hostname primarynode.openstack.na-ca-1.cloud.sap
(11 rows affected)
'sap_status path' now shows replication paths active from the primary (Toronto) to the secondary (London) and the DR (Offsite):
1> sap_status path
2> go
PATH NAME VALUE INFO
---------------------- ------------------------- ----------------------- ------------------------------------------------------------------------------------
Start Time 2022-12-01 19:38:21.842 Time command started executing.
Elapsed Time 00:00:01 Command execution time.
Offsite Hostname drnode Logical host name.
Offsite HADR Status DR Standby : Inactive Identify the primary and standby sites.
Offsite Synchronization Mode Asynchronous The configured Synchronization Mode value.
Offsite Synchronization State Inactive Synchronization Mode in which replication is currently operating.
Offsite Distribution Mode Local Configured value for the distribution_mode replication model property.
Offsite Replication Server Status Active The status of Replication Server.
London Hostname companionnode Logical host name.
London HADR Status Standby : Inactive Identify the primary and standby sites.
London Synchronization Mode Synchronous The configured Synchronization Mode value.
London Synchronization State Inactive Synchronization Mode in which replication is currently operating.
London Distribution Mode Remote Configured value for the distribution_mode replication model property.
London Replication Server Status Active The status of Replication Server.
Toronto Hostname primarynode Logical host name.
Toronto HADR Status Primary : Active Identify the primary and standby sites.
Toronto Synchronization Mode Synchronous The configured Synchronization Mode value.
Toronto Synchronization State Synchronous Synchronization Mode in which replication is currently operating.
Toronto Distribution Mode Remote Configured value for the distribution_mode replication model property.
Toronto Replication Server Status Active The status of Replication Server.
London.Offsite.DEM State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Offsite.DEM Latency Time Unknown No latency information for database 'DEM'.
London.Offsite.DEM Latency Unknown No latency information for database 'DEM'.
London.Offsite.DEM Commit Time Unknown No last commit time for the database 'DEM'.
London.Offsite.DEM Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Offsite.DEM Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Offsite.master State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Offsite.master Latency Time Unknown No latency information for database 'master'.
London.Offsite.master Latency Unknown No latency information for database 'master'.
London.Offsite.master Commit Time Unknown No last commit time for the database 'master'.
London.Offsite.master Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Offsite.master Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Offsite.tpcc State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Offsite.tpcc Latency Time Unknown No latency information for database 'tpcc'.
London.Offsite.tpcc Latency Unknown No latency information for database 'tpcc'.
London.Offsite.tpcc Commit Time Unknown No last commit time for the database 'tpcc'.
London.Offsite.tpcc Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Offsite.tpcc Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Toronto.DEM State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Toronto.DEM Latency Time Unknown No latency information for database 'DEM'.
London.Toronto.DEM Latency Unknown No latency information for database 'DEM'.
London.Toronto.DEM Commit Time Unknown No last commit time for the database 'DEM'.
London.Toronto.DEM Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Toronto.DEM Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Toronto.master State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Toronto.master Latency Time Unknown No latency information for database 'master'.
London.Toronto.master Latency Unknown No latency information for database 'master'.
London.Toronto.master Commit Time Unknown No last commit time for the database 'master'.
London.Toronto.master Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Toronto.master Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Toronto.tpcc State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Toronto.tpcc Latency Time Unknown No latency information for database 'tpcc'.
London.Toronto.tpcc Latency Unknown No latency information for database 'tpcc'.
London.Toronto.tpcc Commit Time Unknown No last commit time for the database 'tpcc'.
London.Toronto.tpcc Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Toronto.tpcc Drain Status Unknown The drain status of the transaction logs of the primary database server.
Toronto.London.DEM State Active Path is active and replication can occur.
Toronto.London.DEM Latency Time 2022-11-25 16:16:08.560 Time latency last calculated
Toronto.London.DEM Latency 404 Latency (ms)
Toronto.London.DEM Commit Time 2022-11-25 16:16:08.560 Time last commit replicated
Toronto.London.DEM Distribution Path London The path of Replication Server through which transactions travel.
Toronto.London.DEM Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.London.master State Active Path is active and replication can occur.
Toronto.London.master Latency Time 2022-11-25 16:15:01.666 Time latency last calculated
Toronto.London.master Latency 573 Latency (ms)
Toronto.London.master Commit Time 2022-12-01 16:00:22.840 Time last commit replicated
Toronto.London.master Distribution Path London The path of Replication Server through which transactions travel.
Toronto.London.master Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.London.tpcc State Defined Path is defined and ready for materialization.
Toronto.London.tpcc Latency Time Unknown No latency information for database 'tpcc'.
Toronto.London.tpcc Latency Unknown No latency information for database 'tpcc'.
Toronto.London.tpcc Commit Time Unknown No last commit time for the database 'tpcc'.
Toronto.London.tpcc Distribution Path London The path of Replication Server through which transactions travel.
Toronto.London.tpcc Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.Offsite.DEM State Active Path is active and replication can occur.
Toronto.Offsite.DEM Latency Time 2022-11-25 16:16:08.892 Time latency last calculated
Toronto.Offsite.DEM Latency 570 Latency (ms)
Toronto.Offsite.DEM Commit Time 2022-11-25 16:16:08.892 Time last commit replicated
Toronto.Offsite.DEM Distribution Path London The path of Replication Server through which transactions travel.
Toronto.Offsite.DEM Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.Offsite.master State Active Path is active and replication can occur.
Toronto.Offsite.master Latency Time 2022-11-25 16:15:01.926 Time latency last calculated
Toronto.Offsite.master Latency 703 Latency (ms)
Toronto.Offsite.master Commit Time 2022-12-01 16:00:22.980 Time last commit replicated
Toronto.Offsite.master Distribution Path London The path of Replication Server through which transactions travel.
Toronto.Offsite.master Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.Offsite.tpcc State Defined Path is defined and ready for materialization.
Toronto.Offsite.tpcc Latency Time Unknown No latency information for database 'tpcc'.
Toronto.Offsite.tpcc Latency Unknown No latency information for database 'tpcc'.
Toronto.Offsite.tpcc Commit Time Unknown No last commit time for the database 'tpcc'.
Toronto.Offsite.tpcc Distribution Path London The path of Replication Server through which transactions travel.
Toronto.Offsite.tpcc Drain Status Not Applicable The drain status of the transaction logs of the primary database server. Note that although the 'State' from the active (Toronto) to the DR (Offsite) shows as 'Active' it has not been materialized. The state from the active to the standby is also only 'Defined' and must also be materialized.
As there are separate paths to the companion and DR, materialization is performed for each.
Materialize database to Companion:
1> sap_materialize auto, Toronto, London, tpcc
2> go
TASKNAME TYPE VALUE
---------------- --------------------- ------------------------------------------------------------------------------------------
Materialize Start Time Thu Dec 01 19:44:04 UTC 2022
Materialize Elapsed Time 00:00:02
DRExecutorImpl Task Name Materialize
DRExecutorImpl Task State Running
DRExecutorImpl Short Description Materialize database
DRExecutorImpl Long Description Started task 'Materialize' asynchronously.
DRExecutorImpl Additional Info Please execute command 'sap_status task' to determine when task 'Materialize' is complete.
Materialize Task Name Materialize
Materialize Task State Running
Materialize Short Description Materialize database
Materialize Long Description Executing ASE dump and load task for database 'tpcc'.
Materialize Task Start Thu Dec 01 19:44:04 UTC 2022
Materialize Hostname primarynode.openstack.na-ca-1.cloud.sap
ASEDBDumpAndLoad Task Name Ase Dump and Load
ASEDBDumpAndLoad Task State Running
ASEDBDumpAndLoad Short Description Dump and Load an Adaptive Server Enterprise database
ASEDBDumpAndLoad Long Description Dumping Adaptive Server Enterprise database 'tpcc'.
ASEDBDumpAndLoad Current Task Number 1
ASEDBDumpAndLoad Total Number of Tasks 2
ASEDBDumpAndLoad Hostname primarynode.openstack.na-ca-1.cloud.sap
(20 rows affected)
1> sap_status task
2> go
TASKNAME TYPE VALUE
----------- ----------------- ---------------------------------------------------------------------------------------------
Status Start Time Thu Dec 01 19:44:04 UTC 2022
Status Elapsed Time 00:00:51
Materialize Task Name Materialize
Materialize Task State Running
Materialize Short Description Materialize database
Materialize Long Description Waiting 10 seconds: Before checking if Replication Connection 'DEM_London.tpcc' is suspended.
Materialize Task Start Thu Dec 01 19:44:04 UTC 2022
Materialize Hostname primarynode.openstack.na-ca-1.cloud.sap
(8 rows affected)
1> sap_status task
2> go
TASKNAME TYPE VALUE
----------- ----------------- ------------------------------------------------------------------------------------------------
Status Start Time Thu Dec 01 19:44:04 UTC 2022
Status Elapsed Time 00:01:12
Materialize Task Name Materialize
Materialize Task State Completed
Materialize Short Description Materialize database
Materialize Long Description Completed automatic materialization of database 'tpcc' from source 'Toronto' to target 'London'.
Materialize Task Start Thu Dec 01 19:44:04 UTC 2022
Materialize Task End Thu Dec 01 19:45:16 UTC 2022
Materialize Hostname primarynode.openstack.na-ca-1.cloud.sap
(9 rows affected)Materialize database to DR:
1> sap_materialize auto, Toronto, Offsite, tpcc
2> go
TASKNAME TYPE VALUE
------------------------------ ----------------- ------------------------------------------------------------------------------------------------------
Materialize Start Time Thu Dec 01 19:47:23 UTC 2022
Materialize Elapsed Time 00:00:02
DRExecutorImpl Task Name Materialize
DRExecutorImpl Task State Running
DRExecutorImpl Short Description Materialize database
DRExecutorImpl Long Description Started task 'Materialize' asynchronously.
DRExecutorImpl Additional Info Please execute command 'sap_status task' to determine when task 'Materialize' is complete.
Materialize Task Name Materialize
Materialize Task State Running
Materialize Short Description Materialize database
Materialize Long Description A preventive measure to help validate the success of materialization succeeded on the database 'tpcc'.
Materialize Task Start Thu Dec 01 19:47:23 UTC 2022
Materialize Hostname primarynode.openstack.na-ca-1.cloud.sap
ConfigureInactiveSubscriptions Task Name ConfigureInactiveSubscriptions
ConfigureInactiveSubscriptions Task State Running
ConfigureInactiveSubscriptions Short Description Configure inactive subscriptions.
ConfigureInactiveSubscriptions Long Description Creating inactive subscriptions for database 'tpcc' from source 'Toronto' to target 'Offsite'.
ConfigureInactiveSubscriptions Hostname primarynode.openstack.na-ca-1.cloud.sap
ResumeDSI Task Name Resume DSI Connection
ResumeDSI Task State Running
ResumeDSI Short Description Resume the Replication Server connection to a database.
ResumeDSI Long Description Waiting 1 seconds: Waiting 10 seconds before checking if the command executed successfully.
ResumeDSI Hostname primarynode.openstack.na-ca-1.cloud.sap
(23 rows affected)
1> sap_status task
2> go
TASKNAME TYPE VALUE
----------------- --------------------- -----------------------------------------------------
Status Start Time Thu Dec 01 19:47:23 UTC 2022
Status Elapsed Time 00:00:12
Materialize Task Name Materialize
Materialize Task State Running
Materialize Short Description Materialize database
Materialize Long Description Executing ASE dump and load task for database 'tpcc'.
Materialize Task Start Thu Dec 01 19:47:23 UTC 2022
Materialize Hostname primarynode.openstack.na-ca-1.cloud.sap
ASEDBDumpAndLoad Task Name Ase Dump and Load
ASEDBDumpAndLoad Task State Running
ASEDBDumpAndLoad Short Description Dump and Load an Adaptive Server Enterprise database
ASEDBDumpAndLoad Long Description Loading Adaptive Server Enterprise database 'tpcc'.
ASEDBDumpAndLoad Current Task Number 2
ASEDBDumpAndLoad Total Number of Tasks 2
ASEDBDumpAndLoad Hostname primarynode.openstack.na-ca-1.cloud.sap
ASESingleUserMode Task Name Change single user database option.
ASESingleUserMode Task State Running
ASESingleUserMode Short Description Change the database option single user.
ASESingleUserMode Long Description Waiting 10 seconds: Killing spid in progress.
ASESingleUserMode Hostname primarynode.openstack.na-ca-1.cloud.sap
(20 rows affected)
1> sap_status task
2> go
TASKNAME TYPE VALUE
---------------- --------------------- -----------------------------------------------------
Status Start Time Thu Dec 01 19:47:23 UTC 2022
Status Elapsed Time 00:00:27
Materialize Task Name Materialize
Materialize Task State Running
Materialize Short Description Materialize database
Materialize Long Description Executing ASE dump and load task for database 'tpcc'.
Materialize Task Start Thu Dec 01 19:47:23 UTC 2022
Materialize Hostname primarynode.openstack.na-ca-1.cloud.sap
ASEDBDumpAndLoad Task Name Ase Dump and Load
ASEDBDumpAndLoad Task State Running
ASEDBDumpAndLoad Short Description Dump and Load an Adaptive Server Enterprise database
ASEDBDumpAndLoad Long Description Loading Adaptive Server Enterprise database 'tpcc'.
ASEDBDumpAndLoad Current Task Number 2
ASEDBDumpAndLoad Total Number of Tasks 2
ASEDBDumpAndLoad Hostname primarynode.openstack.na-ca-1.cloud.sap
(15 rows affected)
1> sap_status task
2> go
TASKNAME TYPE VALUE
----------- ----------------- ----------------------------------------------------------------------------------------------
Status Start Time Thu Dec 01 19:47:23 UTC 2022
Status Elapsed Time 00:01:00
Materialize Task Name Materialize
Materialize Task State Running
Materialize Short Description Materialize database
Materialize Long Description Waiting 10 seconds: Before checking if Replication Connection 'DEM_Offsite.tpcc' is suspended.
Materialize Task Start Thu Dec 01 19:47:23 UTC 2022
Materialize Hostname primarynode.openstack.na-ca-1.cloud.sap
(8 rows affected)
1> sap_status task
2> go
TASKNAME TYPE VALUE
----------- ----------------- -------------------------------------------------------------------------------------------------
Status Start Time Thu Dec 01 19:47:23 UTC 2022
Status Elapsed Time 00:01:14
Materialize Task Name Materialize
Materialize Task State Completed
Materialize Short Description Materialize database
Materialize Long Description Completed automatic materialization of database 'tpcc' from source 'Toronto' to target 'Offsite'.
Materialize Task Start Thu Dec 01 19:47:23 UTC 2022
Materialize Task End Thu Dec 01 19:48:37 UTC 2022
Materialize Hostname primarynode.openstack.na-ca-1.cloud.sap
(9 rows affected)'sap_status_path' now shows:
1> sap_status path
2> go
PATH NAME VALUE INFO
---------------------- ------------------------- ----------------------- ------------------------------------------------------------------------------------
Start Time 2022-12-01 19:51:20.352 Time command started executing.
Elapsed Time 00:00:01 Command execution time.
Offsite Hostname drnode Logical host name.
Offsite HADR Status DR Standby : Inactive Identify the primary and standby sites.
Offsite Synchronization Mode Asynchronous The configured Synchronization Mode value.
Offsite Synchronization State Inactive Synchronization Mode in which replication is currently operating.
Offsite Distribution Mode Local Configured value for the distribution_mode replication model property.
Offsite Replication Server Status Active The status of Replication Server.
London Hostname companionnode Logical host name.
London HADR Status Standby : Inactive Identify the primary and standby sites.
London Synchronization Mode Synchronous The configured Synchronization Mode value.
London Synchronization State Inactive Synchronization Mode in which replication is currently operating.
London Distribution Mode Remote Configured value for the distribution_mode replication model property.
London Replication Server Status Active The status of Replication Server.
Toronto Hostname primarynode Logical host name.
Toronto HADR Status Primary : Active Identify the primary and standby sites.
Toronto Synchronization Mode Synchronous The configured Synchronization Mode value.
Toronto Synchronization State Synchronous Synchronization Mode in which replication is currently operating.
Toronto Distribution Mode Remote Configured value for the distribution_mode replication model property.
Toronto Replication Server Status Active The status of Replication Server.
London.Offsite.DEM State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Offsite.DEM Latency Time Unknown No latency information for database 'DEM'.
London.Offsite.DEM Latency Unknown No latency information for database 'DEM'.
London.Offsite.DEM Commit Time Unknown No last commit time for the database 'DEM'.
London.Offsite.DEM Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Offsite.DEM Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Offsite.master State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Offsite.master Latency Time Unknown No latency information for database 'master'.
London.Offsite.master Latency Unknown No latency information for database 'master'.
London.Offsite.master Commit Time Unknown No last commit time for the database 'master'.
London.Offsite.master Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Offsite.master Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Offsite.tpcc State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Offsite.tpcc Latency Time Unknown No latency information for database 'tpcc'.
London.Offsite.tpcc Latency Unknown No latency information for database 'tpcc'.
London.Offsite.tpcc Commit Time Unknown No last commit time for the database 'tpcc'.
London.Offsite.tpcc Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Offsite.tpcc Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Toronto.DEM State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Toronto.DEM Latency Time Unknown No latency information for database 'DEM'.
London.Toronto.DEM Latency Unknown No latency information for database 'DEM'.
London.Toronto.DEM Commit Time Unknown No last commit time for the database 'DEM'.
London.Toronto.DEM Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Toronto.DEM Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Toronto.master State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Toronto.master Latency Time Unknown No latency information for database 'master'.
London.Toronto.master Latency Unknown No latency information for database 'master'.
London.Toronto.master Commit Time Unknown No last commit time for the database 'master'.
London.Toronto.master Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Toronto.master Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Toronto.tpcc State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Toronto.tpcc Latency Time Unknown No latency information for database 'tpcc'.
London.Toronto.tpcc Latency Unknown No latency information for database 'tpcc'.
London.Toronto.tpcc Commit Time Unknown No last commit time for the database 'tpcc'.
London.Toronto.tpcc Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Toronto.tpcc Drain Status Unknown The drain status of the transaction logs of the primary database server.
Toronto.London.DEM State Active Path is active and replication can occur.
Toronto.London.DEM Latency Time 2022-11-25 16:16:08.552 Time latency last calculated
Toronto.London.DEM Latency 400 Latency (ms)
Toronto.London.DEM Commit Time 2022-11-25 16:16:08.552 Time last commit replicated
Toronto.London.DEM Distribution Path London The path of Replication Server through which transactions travel.
Toronto.London.DEM Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.London.master State Active Path is active and replication can occur.
Toronto.London.master Latency Time 2022-11-25 16:15:01.666 Time latency last calculated
Toronto.London.master Latency 573 Latency (ms)
Toronto.London.master Commit Time 2022-12-01 16:00:22.840 Time last commit replicated
Toronto.London.master Distribution Path London The path of Replication Server through which transactions travel.
Toronto.London.master Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.London.tpcc State Active Path is active and replication can occur.
Toronto.London.tpcc Latency Time 2022-12-01 19:48:38.272 Time latency last calculated
Toronto.London.tpcc Latency 300 Latency (ms)
Toronto.London.tpcc Commit Time 2022-12-01 19:48:38.272 Time last commit replicated
Toronto.London.tpcc Distribution Path London The path of Replication Server through which transactions travel.
Toronto.London.tpcc Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.Offsite.DEM State Active Path is active and replication can occur.
Toronto.Offsite.DEM Latency Time 2022-11-25 16:16:08.884 Time latency last calculated
Toronto.Offsite.DEM Latency 566 Latency (ms)
Toronto.Offsite.DEM Commit Time 2022-11-25 16:16:08.884 Time last commit replicated
Toronto.Offsite.DEM Distribution Path London The path of Replication Server through which transactions travel.
Toronto.Offsite.DEM Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.Offsite.master State Active Path is active and replication can occur.
Toronto.Offsite.master Latency Time 2022-11-25 16:15:01.918 Time latency last calculated
Toronto.Offsite.master Latency 699 Latency (ms)
Toronto.Offsite.master Commit Time 2022-12-01 16:00:22.972 Time last commit replicated
Toronto.Offsite.master Distribution Path London The path of Replication Server through which transactions travel.
Toronto.Offsite.master Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.Offsite.tpcc State Active Path is active and replication can occur.
Toronto.Offsite.tpcc Latency Time 2022-12-01 19:48:38.618 Time latency last calculated
Toronto.Offsite.tpcc Latency 473 Latency (ms)
Toronto.Offsite.tpcc Commit Time 2022-12-01 19:48:38.624 Time last commit replicated
Toronto.Offsite.tpcc Distribution Path London The path of Replication Server through which transactions travel.
Toronto.Offsite.tpcc Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
(92 rows affected)
All paths from active to standby and DR are now in 'Active' status.
Testing HADR
SQL DML and DDL statements are now being replicated, this can be done by connecting an application to the primary (active) database and performing operations. The application used here is beyond the scope of this blog, but any application can be used.
In this case, a Java JDBC application using SAP jConnect 16.0 is used and the application is not HA-aware. No additional connection properties are added to the base URL: "jdbc:sybase:Tds:primarynode.watpm.c.na-ca-1.cloud.sap:5000"
All DDL statements executed by the application to create tables, indexes, etc, are replicated to both the standby and the DR. This can be demonstrated simply by running a command such as the following on each ASE instance (PRIMARY_ASE, COMPANION_ASE, DR_ASE):
use tpcc
go
select name from sysobjects where type = 'U'
go
1> use tpcc
2> go
1> select name from sysobjects where type='U'
2> go
name
------------------
rs_lastcommit
rs_threads
rs_ticket_history
rs_mat_status
rs_dbversion
TPCC_CONFIG
CUSTOMER
DISTRICT
HISTORY
ITEM
NEW_ORDER
ORDERS
ORDER_LINE
STOCK
STOCK_PHOTO
WAREHOUSE
(16 rows affected)
Similarly, for DML, after loading data using batch inserts (java.sql.PreparedStatement.addBatch(), java.sql.PreparedStatement.executeBatch()) and then running the application to perform data changes (java.sql.PreparedStatement.executeUpdate()), the row counts for each table should match in each instance. e.g.
1> use tpcc
2> go
1> select count(*) from ORDER_LINE
2> go
-----------
900016
Planned Failover
To perform a planned failover, 4 steps must take place with commands issued to the primary or companion RMA:
- sap_failover <primary>, <companion>, <timeout>
- sap_failover_drain_to_dr <timeout>
- sap_host_available <dr site>
- sap_host_available <primary>
'sap_status path' should be issued between each command to ensure all tasks have been completed before proceeding to the next step.
1. Start the failover:
1> sap_failover Toronto, London, 120
2> go
TASKNAME TYPE VALUE
-------------- --------------------- -------------------------------------------------------------------------------------------------------
Failover Start Time Tue Dec 06 16:13:35 UTC 2022
Failover Elapsed Time 00:00:02
DRExecutorImpl Task Name Failover
DRExecutorImpl Task State Running
DRExecutorImpl Short Description Failover makes the current standby ASE as the primary server.
DRExecutorImpl Long Description Started task 'Failover' asynchronously.
DRExecutorImpl Additional Info Please execute command 'sap_status task' to determine when task 'Failover' is complete.
Failover Task Name Failover
Failover Task State Running
Failover Short Description Failover makes the current standby ASE as the primary server.
Failover Long Description Issuing commands to verify the DSI thread in the replication server at host 'London' is up and running.
Failover Current Task Number 3
Failover Total Number of Tasks 18
Failover Task Start Tue Dec 06 16:13:35 UTC 2022
Failover Hostname primarynode.openstack.na-ca-1.cloud.sap
(15 rows affected)
> sap_status task
2> go
TASKNAME TYPE VALUE
---------- --------------------- -----------------------------------------------------------------------------------------------------------------------
Status Start Time Tue Dec 06 16:13:35 UTC 2022
Status Elapsed Time 00:00:10
Failover Task Name Failover
Failover Task State Completed
Failover Short Description Failover makes the current standby ASE as the primary server.
Failover Long Description Failover from source 'Toronto' to target 'London' is complete. The target may be unquiesced.
Failover Additional Info Please run command 'sap_failover_drain_to_dr <number_of_seconds_before_timout>' to complete the failover for all hosts.
Failover Current Task Number 15
Failover Total Number of Tasks 15
Failover Task Start Tue Dec 06 16:13:35 UTC 2022
Failover Task End Tue Dec 06 16:13:45 UTC 2022
Failover Hostname primarynode.openstack.na-ca-1.cloud.sap
2. Drain any remaining transactions to DR:
1> sap_failover_drain_to_dr 120
2> go
TASKNAME TYPE VALUE
--------------------- --------------------- -----------------------------------------------------------------------------------------------------
Failover drain to DR. Start Time Tue Dec 06 16:16:16 UTC 2022
Failover drain to DR. Elapsed Time 00:00:02
DRExecutorImpl Task Name Failover drain to DR.
DRExecutorImpl Task State Running
DRExecutorImpl Short Description Failover drain to DR deactivate old replication path and activate new replication path for all hosts.
DRExecutorImpl Long Description Started task 'Failover drain to DR.' asynchronously.
DRExecutorImpl Additional Info Please execute command 'sap_status task' to determine when task 'Failover drain to DR.' is complete.
FailoverRemaining Task Name Failover drain to DR.
FailoverRemaining Task State Running
FailoverRemaining Short Description Failover drain to DR deactivate old replication path and activate new replication path for all hosts.
FailoverRemaining Long Description Issuing command to resume the DIST.
FailoverRemaining Current Task Number 5
FailoverRemaining Total Number of Tasks 7
FailoverRemaining Task Start Tue Dec 06 16:16:16 UTC 2022
FailoverRemaining Hostname primarynode.openstack.na-ca-1.cloud.sap
(15 rows affected)
1> sap_status task
2> go
TASKNAME TYPE VALUE
----------------- --------------------- -----------------------------------------------------------------------------------------------------
Status Start Time Tue Dec 06 16:16:16 UTC 2022
Status Elapsed Time 00:00:05
FailoverRemaining Task Name Failover drain to DR.
FailoverRemaining Task State Completed
FailoverRemaining Short Description Failover drain to DR deactivate old replication path and activate new replication path for all hosts.
FailoverRemaining Long Description Marking old replication path finished.
FailoverRemaining Additional Info Please run command 'sap_host_available' to complete the failover operation.
FailoverRemaining Current Task Number 7
FailoverRemaining Total Number of Tasks 7
FailoverRemaining Task Start Tue Dec 06 16:16:16 UTC 2022
FailoverRemaining Task End Tue Dec 06 16:16:21 UTC 2022
FailoverRemaining Hostname primarynode.openstack.na-ca-1.cloud.sap
(12 rows affected)
1>
Replication from the now active (companion) node back to the stanby (primary) and to the DR shows as suspended:
1> sap_status path
2> go
PATH NAME VALUE INFO
---------------------- ----------------------------- ----------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Start Time 2022-12-06 16:17:51.325 Time command started executing.
Elapsed Time 00:00:01 Command execution time.
Offsite Hostname drnode Logical host name.
Offsite HADR Status DR Standby : Inactive Identify the primary and standby sites.
Offsite Synchronization Mode Asynchronous The configured Synchronization Mode value.
Offsite Synchronization State Inactive Synchronization Mode in which replication is currently operating.
Offsite Distribution Mode Local Configured value for the distribution_mode replication model property.
Offsite Replication Server Status Active The status of Replication Server.
London Hostname companionnode Logical host name.
London HADR Status Primary : Active Identify the primary and standby sites.
London Synchronization Mode Synchronous The configured Synchronization Mode value.
London Synchronization State Unknown Replication Synchronization Mode is currently operating as 'Unknown' instead of the configured value 'Synchronous'. Please refer to Replication Server logs for more details.
London Distribution Mode Remote Configured value for the distribution_mode replication model property.
London Replication Server Status Active The status of Replication Server.
Toronto Hostname primarynode Logical host name.
Toronto HADR Status Standby : Inactive Identify the primary and standby sites.
Toronto Synchronization Mode Synchronous The configured Synchronization Mode value.
Toronto Synchronization State Inactive Synchronization Mode in which replication is currently operating.
Toronto Distribution Mode Remote Configured value for the distribution_mode replication model property.
Toronto Replication Server Status Active The status of Replication Server.
London.Offsite.DEM State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Offsite.DEM Additional Info Additional Info The REPLICATION AGENT connection in the Replication Server on the primarynode host to 'DEM_London_R1.DEM' is suspended.
London.Offsite.DEM Failing Command Error Message Failing Command Error Message The DSI connection in the Replication Server on the Offsite host to 'DEM_Offsite.DEM' is suspended.
London.Offsite.DEM Latency Time 2022-12-06 16:17:52.326 Time latency last calculated
London.Offsite.DEM Latency 246257 Latency (ms)
London.Offsite.DEM Commit Time 2022-11-25 16:15:36.420 Time last commit replicated
London.Offsite.DEM Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Offsite.DEM Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Offsite.master State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Offsite.master Additional Info Additional Info The REPLICATION AGENT connection in the Replication Server on the primarynode host to 'DEM_London_R1.master' is suspended.
London.Offsite.master Failing Command Error Message Failing Command Error Message The DSI connection in the Replication Server on the Offsite host to 'DEM_Offsite.master' is suspended.
London.Offsite.master Latency Time 2022-12-06 16:17:52.512 Time latency last calculated
London.Offsite.master Latency 246350 Latency (ms)
London.Offsite.master Commit Time 2022-11-25 16:14:36.426 Time last commit replicated
London.Offsite.master Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Offsite.master Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Offsite.tpcc State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Offsite.tpcc Additional Info Additional Info The REPLICATION AGENT connection in the Replication Server on the primarynode host to 'DEM_London_R1.tpcc' is suspended.
London.Offsite.tpcc Failing Command Error Message Failing Command Error Message The DSI connection in the Replication Server on the Offsite host to 'DEM_Offsite.tpcc' is suspended.
London.Offsite.tpcc Latency Time 2022-12-06 16:17:52.712 Time latency last calculated
London.Offsite.tpcc Latency 246450 Latency (ms)
London.Offsite.tpcc Commit Time 2022-12-01 19:48:05.546 Time last commit replicated
London.Offsite.tpcc Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Offsite.tpcc Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Toronto.DEM State Incomplete Failover is incomplete.
London.Toronto.DEM Additional Info Additional Info Path has been activated by failover. Replication cannot occur until the standby host 'Toronto' is made available.
London.Toronto.DEM Failing Command Error Message Failing Command Error Message The DSI connection in the Replication Server on the Toronto host to 'DEM_Toronto.DEM' is suspended.
London.Toronto.DEM Latency Time Unknown No latency information for database 'DEM'.
London.Toronto.DEM Latency Unknown No latency information for database 'DEM'.
London.Toronto.DEM Commit Time 2022-11-15 20:50:41.794 Time last commit replicated
London.Toronto.DEM Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Toronto.DEM Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Toronto.master State Incomplete Failover is incomplete.
London.Toronto.master Additional Info Additional Info Path has been activated by failover. Replication cannot occur until the standby host 'Toronto' is made available.
London.Toronto.master Failing Command Error Message Failing Command Error Message The DSI connection in the Replication Server on the Toronto host to 'DEM_Toronto.master' is suspended.
London.Toronto.master Latency Time Unknown No latency information for database 'master'.
London.Toronto.master Latency Unknown No latency information for database 'master'.
London.Toronto.master Commit Time 2022-11-15 20:49:07.954 Time last commit replicated
London.Toronto.master Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Toronto.master Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Toronto.tpcc State Incomplete Failover is incomplete.
London.Toronto.tpcc Additional Info Additional Info Path has been activated by failover. Replication cannot occur until the standby host 'Toronto' is made available.
London.Toronto.tpcc Failing Command Error Message Failing Command Error Message The DSI connection in the Replication Server on the Toronto host to 'DEM_Toronto.tpcc' is suspended.
London.Toronto.tpcc Latency Time Unknown No latency information for database 'tpcc'.
London.Toronto.tpcc Latency Unknown No latency information for database 'tpcc'.
London.Toronto.tpcc Commit Time 2022-12-01 19:34:05.980 Time last commit replicated
London.Toronto.tpcc Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Toronto.tpcc Drain Status Unknown The drain status of the transaction logs of the primary database server.
Toronto.London.DEM State Suspended Path is suspended (DSI). Transactions are not being replicated.
Toronto.London.DEM Failing Command Error Message Failing Command Error Message The DSI connection in the Replication Server on the London host to 'DEM_London.DEM' is suspended.
Toronto.London.DEM Latency Time Unknown No latency information for database 'DEM'.
Toronto.London.DEM Latency Unknown No latency information for database 'DEM'.
Toronto.London.DEM Commit Time Unknown No last commit time for the database 'DEM'.
Toronto.London.DEM Distribution Path London The path of Replication Server through which transactions travel.
Toronto.London.DEM Drain Status Drained The drain status of the transaction logs of the primary database server.
Toronto.London.master State Suspended Path is suspended (DSI). Transactions are not being replicated.
Toronto.London.master Failing Command Error Message Failing Command Error Message The DSI connection in the Replication Server on the London host to 'DEM_London.master' is suspended.
Toronto.London.master Latency Time Unknown No latency information for database 'master'.
Toronto.London.master Latency Unknown No latency information for database 'master'.
Toronto.London.master Commit Time Unknown No last commit time for the database 'master'.
Toronto.London.master Distribution Path London The path of Replication Server through which transactions travel.
Toronto.London.master Drain Status Drained The drain status of the transaction logs of the primary database server.
Toronto.London.tpcc State Suspended Path is suspended (DSI). Transactions are not being replicated.
Toronto.London.tpcc Failing Command Error Message Failing Command Error Message The DSI connection in the Replication Server on the London host to 'DEM_London.tpcc' is suspended.
Toronto.London.tpcc Latency Time Unknown No latency information for database 'tpcc'.
Toronto.London.tpcc Latency Unknown No latency information for database 'tpcc'.
Toronto.London.tpcc Commit Time Unknown No last commit time for the database 'tpcc'.
Toronto.London.tpcc Distribution Path London The path of Replication Server through which transactions travel.
Toronto.London.tpcc Drain Status Drained The drain status of the transaction logs of the primary database server.
Toronto.Offsite.DEM State Suspended Path is suspended (DSI). Transactions are not being replicated.
Toronto.Offsite.DEM Failing Command Error Message Failing Command Error Message The DSI connection in the Replication Server on the Offsite host to 'DEM_Offsite.DEM' is suspended.
Toronto.Offsite.DEM Latency Time Unknown No latency information for database 'DEM'.
Toronto.Offsite.DEM Latency Unknown No latency information for database 'DEM'.
Toronto.Offsite.DEM Commit Time Unknown No last commit time for the database 'DEM'.
Toronto.Offsite.DEM Distribution Path London The path of Replication Server through which transactions travel.
Toronto.Offsite.DEM Drain Status Drained The drain status of the transaction logs of the primary database server.
Toronto.Offsite.master State Suspended Path is suspended (DSI). Transactions are not being replicated.
Toronto.Offsite.master Failing Command Error Message Failing Command Error Message The DSI connection in the Replication Server on the Offsite host to 'DEM_Offsite.master' is suspended.
Toronto.Offsite.master Latency Time Unknown No latency information for database 'master'.
Toronto.Offsite.master Latency Unknown No latency information for database 'master'.
Toronto.Offsite.master Commit Time Unknown No last commit time for the database 'master'.
Toronto.Offsite.master Distribution Path London The path of Replication Server through which transactions travel.
Toronto.Offsite.master Drain Status Drained The drain status of the transaction logs of the primary database server.
Toronto.Offsite.tpcc State Suspended Path is suspended (DSI). Transactions are not being replicated.
Toronto.Offsite.tpcc Failing Command Error Message Failing Command Error Message The DSI connection in the Replication Server on the Offsite host to 'DEM_Offsite.tpcc' is suspended.
Toronto.Offsite.tpcc Latency Time Unknown No latency information for database 'tpcc'.
Toronto.Offsite.tpcc Latency Unknown No latency information for database 'tpcc'.
Toronto.Offsite.tpcc Commit Time Unknown No last commit time for the database 'tpcc'.
Toronto.Offsite.tpcc Distribution Path London The path of Replication Server through which transactions travel.
Toronto.Offsite.tpcc Drain Status Drained The drain status of the transaction logs of the primary database server.
(110 rows affected)
Two more commands are issued to mark those hosts available to receive changes from the now active (companion) node:
3/4. Make nodes available to accept transactions:
1> sap_host_available Offsite
2> go
TASKNAME TYPE VALUE
------------- --------------------- -------------------------------------------------------------------------------------------------------
HostAvailable Start Time Tue Dec 06 16:21:33 UTC 2022
HostAvailable Elapsed Time 00:00:56
HostAvailable Task Name HostAvailable
HostAvailable Task State Completed
HostAvailable Short Description Resets the original source logical host when it is available after failover.
HostAvailable Long Description Completed the reset process of logical host 'Offsite' receiving replication from logical host 'London'.
HostAvailable Current Task Number 14
HostAvailable Total Number of Tasks 14
HostAvailable Task Start Tue Dec 06 16:21:33 UTC 2022
HostAvailable Task End Tue Dec 06 16:22:29 UTC 2022
HostAvailable Hostname primarynode.openstack.na-ca-1.cloud.sap
(11 rows affected)
1> sap_host_available Toronto
2> go
TASKNAME TYPE VALUE
------------- --------------------- -------------------------------------------------------------------------------------------------------
HostAvailable Start Time Tue Dec 06 16:23:09 UTC 2022
HostAvailable Elapsed Time 00:01:07
HostAvailable Task Name HostAvailable
HostAvailable Task State Completed
HostAvailable Short Description Resets the original source logical host when it is available after failover.
HostAvailable Long Description Completed the reset process of logical host 'Toronto' receiving replication from logical host 'London'.
HostAvailable Current Task Number 13
HostAvailable Total Number of Tasks 13
HostAvailable Task Start Tue Dec 06 16:23:09 UTC 2022
HostAvailable Task End Tue Dec 06 16:24:16 UTC 2022
HostAvailable Hostname primarynode.openstack.na-ca-1.cloud.sap
(11 rows affected)
As the standby node (primary) is available for network connections, any attempt to login with a non-sa_role will be redirected to the active (companion) node. Running the previous application with no changes redirects the login to the active (companion) node automatically. Again, this can be confirmed by running comparison queries on each node to check the data. A simple example:
1> select count(*) from ORDER_LINE
2> go
-----------
900230
(1 row affected)
Planned Failback
Similar to planned failover, performing a planned failback also takes 4 steps using commands issued to the primary or companion RMA:
- sap_failover <companion>, <primary>, <timeout>
- sap_failover_drain_to_dr <timeout>
- sap_host_available <dr site>
- sap_host_available <companion>
Again, 'sap_status task' should be issued between each command to ensure all tasks have been completed before proceeding to the next step.
1. Start the failback:
1> sap_failover London, Toronto, 120
2> go
TASKNAME TYPE VALUE
-------------- --------------------- ---------------------------------------------------------------------------------------------------
Failover Start Time Tue Dec 06 18:37:42 UTC 2022
Failover Elapsed Time 00:00:02
DRExecutorImpl Task Name Failover
DRExecutorImpl Task State Running
DRExecutorImpl Short Description Failover makes the current standby ASE as the primary server.
DRExecutorImpl Long Description Started task 'Failover' asynchronously.
DRExecutorImpl Additional Info Please execute command 'sap_status task' to determine when task 'Failover' is complete.
Failover Task Name Failover
Failover Task State Running
Failover Short Description Failover makes the current standby ASE as the primary server.
Failover Long Description Sending end of data markers to participating databases in the source ASE for logical host 'London'.
Failover Current Task Number 5
Failover Total Number of Tasks 18
Failover Task Start Tue Dec 06 18:37:42 UTC 2022
Failover Hostname primarynode.openstack.na-ca-1.cloud.sap
(15 rows a1> sap_status task
2> go
TASKNAME TYPE VALUE
---------- --------------------- -----------------------------------------------------------------------------------------------------------------------
Status Start Time Tue Dec 06 18:37:42 UTC 2022
Status Elapsed Time 00:00:09
Failover Task Name Failover
Failover Task State Completed
Failover Short Description Failover makes the current standby ASE as the primary server.
Failover Long Description Failover from source 'London' to target 'Toronto' is complete. The target may be unquiesced.
Failover Additional Info Please run command 'sap_failover_drain_to_dr <number_of_seconds_before_timout>' to complete the failover for all hosts.
Failover Current Task Number 15
Failover Total Number of Tasks 15
Failover Task Start Tue Dec 06 18:37:42 UTC 2022
Failover Task End Tue Dec 06 18:37:51 UTC 2022
Failover Hostname primarynode.openstack.na-ca-1.cloud.sap
(12 rows affected)
ffected)
2. Drain any remaining transactions to DR:
1> sap_failover_drain_to_dr 120
2> go
TASKNAME TYPE VALUE
--------------------- --------------------- -----------------------------------------------------------------------------------------------------
Failover drain to DR. Start Time Tue Dec 06 18:39:52 UTC 2022
Failover drain to DR. Elapsed Time 00:00:02
DRExecutorImpl Task Name Failover drain to DR.
DRExecutorImpl Task State Running
DRExecutorImpl Short Description Failover drain to DR deactivate old replication path and activate new replication path for all hosts.
DRExecutorImpl Long Description Started task 'Failover drain to DR.' asynchronously.
DRExecutorImpl Additional Info Please execute command 'sap_status task' to determine when task 'Failover drain to DR.' is complete.
FailoverRemaining Task Name Failover drain to DR.
FailoverRemaining Task State Running
FailoverRemaining Short Description Failover drain to DR deactivate old replication path and activate new replication path for all hosts.
FailoverRemaining Long Description Issuing command to resume the DIST.
FailoverRemaining Current Task Number 5
FailoverRemaining Total Number of Tasks 7
FailoverRemaining Task Start Tue Dec 06 18:39:52 UTC 2022
FailoverRemaining Hostname primarynode.openstack.na-ca-1.cloud.sap
(15 rows affected)
1> sap_status task
2> go
TASKNAME TYPE VALUE
----------------- --------------------- -----------------------------------------------------------------------------------------------------
Status Start Time Tue Dec 06 18:39:52 UTC 2022
Status Elapsed Time 00:00:04
FailoverRemaining Task Name Failover drain to DR.
FailoverRemaining Task State Completed
FailoverRemaining Short Description Failover drain to DR deactivate old replication path and activate new replication path for all hosts.
FailoverRemaining Long Description Marking old replication path finished.
FailoverRemaining Additional Info Please run command 'sap_host_available' to complete the failover operation.
FailoverRemaining Current Task Number 7
FailoverRemaining Total Number of Tasks 7
FailoverRemaining Task Start Tue Dec 06 18:39:52 UTC 2022
FailoverRemaining Task End Tue Dec 06 18:39:56 UTC 2022
FailoverRemaining Hostname primarynode.openstack.na-ca-1.cloud.sap
(12 rows affected)
3/4. Make nodes available to accept transactions:
1> sap_host_available Offsite
2> go
TASKNAME TYPE VALUE
------------- --------------------- --------------------------------------------------------------------------------------------------------
HostAvailable Start Time Tue Dec 06 18:44:18 UTC 2022
HostAvailable Elapsed Time 00:00:55
HostAvailable Task Name HostAvailable
HostAvailable Task State Completed
HostAvailable Short Description Resets the original source logical host when it is available after failover.
HostAvailable Long Description Completed the reset process of logical host 'Offsite' receiving replication from logical host 'Toronto'.
HostAvailable Current Task Number 14
HostAvailable Total Number of Tasks 14
HostAvailable Task Start Tue Dec 06 18:44:18 UTC 2022
HostAvailable Task End Tue Dec 06 18:45:13 UTC 2022
HostAvailable Hostname primarynode.openstack.na-ca-1.cloud.sap
(11 rows affected)
1> sap_host_available London
2> go
TASKNAME TYPE VALUE
------------- --------------------- -------------------------------------------------------------------------------------------------------
HostAvailable Start Time Tue Dec 06 18:46:23 UTC 2022
HostAvailable Elapsed Time 00:01:07
HostAvailable Task Name HostAvailable
HostAvailable Task State Completed
HostAvailable Short Description Resets the original source logical host when it is available after failover.
HostAvailable Long Description Completed the reset process of logical host 'London' receiving replication from logical host 'Toronto'.
HostAvailable Current Task Number 13
HostAvailable Total Number of Tasks 13
HostAvailable Task Start Tue Dec 06 18:46:23 UTC 2022
HostAvailable Task End Tue Dec 06 18:47:30 UTC 2022
HostAvailable Hostname primarynode.openstack.na-ca-1.cloud.sap
(11 rows affected)
Running the application again with no changes (i.e. against the now-active primary node) yields the same row counts:
1> use tpcc
2> go
1> select count(*) from ORDER_LINE
2> go
-----------
900456
Connecting to an RMA instance on any node shows that the active instance is now, again, the ASE instance on the primary node:
1> sap_status path
2> go
PATH NAME VALUE INFO
---------------------- ------------------------- ----------------------- ------------------------------------------------------------------------------------
Start Time 2022-12-06 21:03:57.040 Time command started executing.
Elapsed Time 00:00:01 Command execution time.
Offsite Hostname drnode Logical host name.
Offsite HADR Status DR Standby : Inactive Identify the primary and standby sites.
Offsite Synchronization Mode Asynchronous The configured Synchronization Mode value.
Offsite Synchronization State Inactive Synchronization Mode in which replication is currently operating.
Offsite Distribution Mode Local Configured value for the distribution_mode replication model property.
Offsite Replication Server Status Active The status of Replication Server.
London Hostname companionnode Logical host name.
London HADR Status Standby : Inactive Identify the primary and standby sites.
London Synchronization Mode Synchronous The configured Synchronization Mode value.
London Synchronization State Inactive Synchronization Mode in which replication is currently operating.
London Distribution Mode Remote Configured value for the distribution_mode replication model property.
London Replication Server Status Active The status of Replication Server.
Toronto Hostname primarynode Logical host name.
Toronto HADR Status Primary : Active Identify the primary and standby sites.
Toronto Synchronization Mode Synchronous The configured Synchronization Mode value.
Toronto Synchronization State Synchronous Synchronization Mode in which replication is currently operating.
Toronto Distribution Mode Remote Configured value for the distribution_mode replication model property.
Toronto Replication Server Status Active The status of Replication Server.
London.Offsite.DEM State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Offsite.DEM Latency Time Unknown No latency information for database 'DEM'.
London.Offsite.DEM Latency Unknown No latency information for database 'DEM'.
London.Offsite.DEM Commit Time Unknown No last commit time for the database 'DEM'.
London.Offsite.DEM Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Offsite.DEM Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Offsite.master State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Offsite.master Latency Time Unknown No latency information for database 'master'.
London.Offsite.master Latency Unknown No latency information for database 'master'.
London.Offsite.master Commit Time Unknown No last commit time for the database 'master'.
London.Offsite.master Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Offsite.master Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Offsite.tpcc State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Offsite.tpcc Latency Time Unknown No latency information for database 'tpcc'.
London.Offsite.tpcc Latency Unknown No latency information for database 'tpcc'.
London.Offsite.tpcc Commit Time Unknown No last commit time for the database 'tpcc'.
London.Offsite.tpcc Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Offsite.tpcc Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Toronto.DEM State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Toronto.DEM Latency Time Unknown No latency information for database 'DEM'.
London.Toronto.DEM Latency Unknown No latency information for database 'DEM'.
London.Toronto.DEM Commit Time Unknown No last commit time for the database 'DEM'.
London.Toronto.DEM Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Toronto.DEM Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Toronto.master State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Toronto.master Latency Time Unknown No latency information for database 'master'.
London.Toronto.master Latency Unknown No latency information for database 'master'.
London.Toronto.master Commit Time Unknown No last commit time for the database 'master'.
London.Toronto.master Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Toronto.master Drain Status Unknown The drain status of the transaction logs of the primary database server.
London.Toronto.tpcc State Suspended Path is suspended (Replication Agent Thread). Transactions are not being replicated.
London.Toronto.tpcc Latency Time Unknown No latency information for database 'tpcc'.
London.Toronto.tpcc Latency Unknown No latency information for database 'tpcc'.
London.Toronto.tpcc Commit Time Unknown No last commit time for the database 'tpcc'.
London.Toronto.tpcc Distribution Path Toronto The path of Replication Server through which transactions travel.
London.Toronto.tpcc Drain Status Unknown The drain status of the transaction logs of the primary database server.
Toronto.London.DEM State Active Path is active and replication can occur.
Toronto.London.DEM Latency Time 2022-12-06 18:47:31.286 Time latency last calculated
Toronto.London.DEM Latency 383 Latency (ms)
Toronto.London.DEM Commit Time 2022-12-06 18:47:31.292 Time last commit replicated
Toronto.London.DEM Distribution Path London The path of Replication Server through which transactions travel.
Toronto.London.DEM Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.London.master State Active Path is active and replication can occur.
Toronto.London.master Latency Time 2022-12-06 18:47:31.292 Time latency last calculated
Toronto.London.master Latency 386 Latency (ms)
Toronto.London.master Commit Time 2022-12-06 18:47:31.292 Time last commit replicated
Toronto.London.master Distribution Path London The path of Replication Server through which transactions travel.
Toronto.London.master Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.London.tpcc State Active Path is active and replication can occur.
Toronto.London.tpcc Latency Time 2022-12-06 18:47:31.292 Time latency last calculated
Toronto.London.tpcc Latency 386 Latency (ms)
Toronto.London.tpcc Commit Time 2022-12-06 19:33:53.852 Time last commit replicated
Toronto.London.tpcc Distribution Path London The path of Replication Server through which transactions travel.
Toronto.London.tpcc Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.Offsite.DEM State Active Path is active and replication can occur.
Toronto.Offsite.DEM Latency Time 2022-12-06 18:47:31.472 Time latency last calculated
Toronto.Offsite.DEM Latency 476 Latency (ms)
Toronto.Offsite.DEM Commit Time 2022-12-06 18:47:31.472 Time last commit replicated
Toronto.Offsite.DEM Distribution Path London The path of Replication Server through which transactions travel.
Toronto.Offsite.DEM Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.Offsite.master State Active Path is active and replication can occur.
Toronto.Offsite.master Latency Time 2022-12-06 18:47:31.466 Time latency last calculated
Toronto.Offsite.master Latency 473 Latency (ms)
Toronto.Offsite.master Commit Time 2022-12-06 18:47:31.466 Time last commit replicated
Toronto.Offsite.master Distribution Path London The path of Replication Server through which transactions travel.
Toronto.Offsite.master Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
Toronto.Offsite.tpcc State Active Path is active and replication can occur.
Toronto.Offsite.tpcc Latency Time 2022-12-06 18:47:31.472 Time latency last calculated
Toronto.Offsite.tpcc Latency 476 Latency (ms)
Toronto.Offsite.tpcc Commit Time 2022-12-06 19:33:53.160 Time last commit replicated
Toronto.Offsite.tpcc Distribution Path London The path of Replication Server through which transactions travel.
Toronto.Offsite.tpcc Drain Status Not Applicable The drain status of the transaction logs of the primary database server.
(92 rows affected)
(continued in Part 3…)
- SAP Managed Tags:
- SAP Adaptive Server Enterprise,
- SAP ASE - HADR Enablement
Labels:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Labels in this area
-
ABAP CDS Views - CDC (Change Data Capture)
2 -
AI
1 -
Analyze Workload Data
1 -
BTP
1 -
Business and IT Integration
2 -
Business application stu
1 -
Business Technology Platform
1 -
Business Trends
1,658 -
Business Trends
93 -
CAP
1 -
cf
1 -
Cloud Foundry
1 -
Confluent
1 -
Customer COE Basics and Fundamentals
1 -
Customer COE Latest and Greatest
3 -
Customer Data Browser app
1 -
Data Analysis Tool
1 -
data migration
1 -
data transfer
1 -
Datasphere
2 -
Event Information
1,400 -
Event Information
66 -
Expert
1 -
Expert Insights
177 -
Expert Insights
299 -
General
1 -
Google cloud
1 -
Google Next'24
1 -
Kafka
1 -
Life at SAP
780 -
Life at SAP
13 -
Migrate your Data App
1 -
MTA
1 -
Network Performance Analysis
1 -
NodeJS
1 -
PDF
1 -
POC
1 -
Product Updates
4,577 -
Product Updates
345 -
Replication Flow
1 -
RisewithSAP
1 -
SAP BTP
1 -
SAP BTP Cloud Foundry
1 -
SAP Cloud ALM
1 -
SAP Cloud Application Programming Model
1 -
SAP Datasphere
2 -
SAP S4HANA Cloud
1 -
SAP S4HANA Migration Cockpit
1 -
Technology Updates
6,872 -
Technology Updates
427 -
Workload Fluctuations
1
Related Content
- Dealy in updating custom values for Visibility Scenario SAP BUILD AUTOMATION in Technology Q&A
- SAP Cloud ALM and Identity Authentication Service (IAS) in Technology Blogs by SAP
- SAP Build Process Automation Pre-built content for Finance Use cases in Technology Blogs by SAP
- Onboarding Users in SAP Quality Issue Resolution in Technology Blogs by SAP
- How to use AI services to translate Picklists in SAP SuccessFactors - An example in Technology Blogs by SAP
Top kudoed authors
| User | Count |
|---|---|
| 41 | |
| 25 | |
| 17 | |
| 14 | |
| 9 | |
| 7 | |
| 6 | |
| 6 | |
| 6 | |
| 6 |
