Configuring SAP HANA Active/Active (Read Enabled) System Replication in a RHEL HA Add-On Cluster

Checking access by using an explicit read-only connection

Verify the connection to the secondary instance by using an explicit read-only connection.

On a cluster node, run the following command.

sudo -i -u ${sid}adm -- \
    hdbsql -n $VIP_SECONDARY -i $INSTNO -d SYSTEMDB -u SYSTEM \
      "select * from m_host_information \
      where key = 'net_hostnames' or key = 'net_ip_addresses'"

The sample output shows that the statement ran on the SAP HANA secondary.

HOST,KEY,VALUE
"cl-h4s-2","net_hostnames","cl-h4s-2"
"cl-h4s-2","net_ip_addresses","10.40.10.132,10.40.10.211"
2 rows selected (overall time 7518 usec; server time 291 usec)

Checking access by using hint-based statement routing

Verify the connection to the secondary instance by using the hint-based statement routing.

1. Run a connection test by using an explicit connection to the SAP HANA primary without an SQL hint.

   On a cluster node, run the following command.

   sudo -i -u ${sid}adm -- \
       hdbsql -n $VIP_PRIMARY -i $INSTNO -d SYSTEMDB -u SYSTEM \
         "select * from m_host_information \
         where key = 'net_hostnames' or key = 'net_ip_addresses'"
   

   The sample output shows that the statement ran on the SAP HANA primary.

     HOST,KEY,VALUE
   "cl-h4s-1","net_hostnames","cl-h4s-1"
   "cl-h4s-1","net_ip_addresses","10.40.10.162,10.40.10.201"
   2 rows selected (overall time 5239 usec; server time 361 usec)
   

2. Run a connection test by using an explicit connection to the SAP HANA primary and the result_lag SQL hint.

   sudo -i -u ${sid}adm -- \
       hdbsql -n $VIP_PRIMARY -i $INSTNO -d SYSTEMDB -u SYSTEM \
         "select * from m_host_information \
         where key = 'net_hostnames' or key = 'net_ip_addresses' \
         with hint(result_lag('hana_sr'))"
   

   The sample output shows that the statement ran on the SAP HANA secondary.

   HOST,KEY,VALUE
   "cl-h4s-2","net_hostnames","cl-h4s-2"
   "cl-h4s-2","net_ip_addresses","10.40.10.132,10.40.10.211"
   2 rows selected (overall time 40.722 msec; server time 16.428 msec)
   

Test1 - Description

Simulate a crash of the primary SAP HANA database instance that is running on NODE1.

Test1 - Prerequisites

• A functional two-node RHEL HA Add-On cluster for SAP HANA system replication.
• Both cluster nodes are active.
• Cluster that is started on NODE1 and NODE2.
• Cluster Resource SAPHana_${SID}_${INSTNO} that is configured with AUTOMATED_REGISTER=false.
• Check SAP HANA System Replication status:
  • Primary SAP HANA database is running on NODE1
  • Secondary SAP HANA database is running on NODE2
  • SAP HANA System Replication is activated and in sync

Test1 - Test procedure

Crash SAP HANA primary by sending a SIGKILL signal as the user ${sid}adm.

On NODE1, run the following command.

sudo -i -u ${sid}adm -- HDB kill-9

Test1 - Expected behavior

• SAP HANA primary instance on NODE1 crashes.
• The cluster detects the stopped primary SAP HANA database and marks the resource as failed.
• The cluster promotes the secondary SAP HANA database on NODE2 to take over as the new primary.
• The cluster releases the virtual IP address on NODE1, and acquires it on the new primary on NODE2.
• After the takeover, the secondary SAP HANA instance is unavailable and the secondary virtual IP address stays on NODE2.
• If an application, such as SAP NetWeaver, is connected to a tenant database of SAP HANA, the application automatically reconnects to the new primary.

Test1 - Recovery procedure

As the cluster resource SAPHana_${SID}_${INSTNO} is configured with AUTOMATED_REGISTER=false, the cluster doesn't restart the failed SAP HANA database, and doesn't register it against the new primary. Which means that the status on the new primary (NODE2) also shows the secondary in status 'CONNECTION TIMEOUT'.

To reregister the previous primary as a new secondary use the following commands.

On NODE1, run the following command.

sudo -i -u ${sid}adm -- \
    hdbnsutil -sr_register \
      --name=${DC1} \
      --remoteHost=${NODE2} \
      --remoteInstance=00 \
      --replicationMode=sync \
      --operationMode=logreplay_readaccess \
      --online

Verify the system replication status:

sudo -i -u ${sid}adm -- \
    hdbnsutil -sr_state

On a cluster node, run the following command to refresh the cluster resource. This command starts the secondary instance.

pcs resource refresh SAPHana_${SID}_${INSTNO}

When the secondary reaches the synced state (SOK), the secondary virtual IP address moves to NODE1.

On a cluster node, run the following command to check the cluster status.

pcs status --full

Test2 - Description

Simulate a crash of the node that is running the primary SAP HANA database.

Test2 - Preparation

Make sure that the Cluster Resource SAPHana_${SID}_${INSTNO} is configured with AUTOMATED_REGISTER=true.

On NODE1, run the following command.

pcs resource update SAPHana_${SID}_${INSTNO} AUTOMATED_REGISTER=true

Verify the AUTOMATED_REGISTER setting in the resource configuration.

pcs resource config SAPHana_${SID}_${INSTNO} | grep Attributes

Test2 - Prerequisites

• A functional two-node RHEL HA Add-On cluster for SAP HANA system replication.
• Both nodes are active.
• Cluster is started on NODE1 and NODE2.
• Check SAP HANA System Replication status.
  • Primary SAP HANA database is running on NODE2
  • Secondary SAP HANA database is running on NODE1
  • SAP HANA System Replication is activated and in sync
  • Secondary virtual IP address is active on NODE1

Test2 - Test procedure

Crash primary on NODE2 by sending a shutoff system request.

On NODE2, run the following command.

sync; echo o > /proc/sysrq-trigger

Test2 - Expected behavior

• NODE2 shuts down.
• The cluster detects the failed node and sets its state to OFFLINE.
• The cluster promotes the secondary SAP HANA database on NODE1 to take over as the new primary.
• The cluster acquires the virtual IP address on NODE1 on the new primary.
• After the takeover, the secondary SAP HANA instance is unavailable and the secondary virtual IP address stays on NODE1.
• If an application, such as SAP NetWeaver, is connected to a tenant database of SAP HANA, the application automatically reconnects to the new primary.

Test2 - Recovery procedure

Log in to the IBM Cloud® Console and start the NODE2 instance. Wait until NODE2 is available again, then restart the cluster framework.

On NODE2, run the following command.

pcs cluster start

pcs status --full

As a cluster resource SAPHana_${SID}_${INSTNO} is configured with AUTOMATED_REGISTER=true, SAP HANA restarts when NODE2 rejoins the cluster and the former primary reregisters as a secondary. When the secondary reaches the synced state (SOK), the secondary virtual IP address moves to NODE2.

Test3 - Description

Simulate a crash of the secondary SAP HANA database.

Test3 - Prerequisites

• A functional two-node RHEL HA Add-On cluster for SAP HANA system replication.
• Both nodes are active.
• Cluster is started on NODE1 and NODE2.
• Cluster Resource SAPHana_${SID}_${INSTNO} is configured with AUTOMATED_REGISTER=true.
• Check SAP HANA System Replication status:
  • Primary SAP HANA database is running on NODE1
  • Secondary SAP HANA database is running on NODE2
  • SAP HANA System Replication is activated and in sync
  • Secondary virtual IP address is active on NODE2

Test3 - Test procedure

Crash SAP HANA secondary by sending a SIGKILL signal as the user ${sid}adm.

On NODE2, run the following command.

sudo -i -u ${sid}adm -- HDB kill-9

Test3 - Expected behavior

• SAP HANA secondary on NODE2 crashes.
• The cluster detects the stopped secondary SAP HANA database and marks the resource as failed.
• The cluster moves the secondary virtual IP address to NODE1.
• The cluster restarts the secondary SAP HANA database.
• The cluster detects that the system replication is in sync again.
• The cluster moves the secondary virtual IP address back to NODE2.

Test3 - Recovery procedure

Wait until the secondary SAP HANA instance starts and syncs again (SOK), then cleanup the failed resource actions as shown in pcs status.

On a cluster node, run the following commands.

pcs resource refresh SAPHana_${SID}_${INSTNO}

pcs status --full

Test4 - Description

Use cluster commands to move the primary instance to the other node for maintenance purposes.

Test4 - Prerequisites

• A functional two-node RHEL HA Add-On cluster for SAP HANA system replication.
• Both nodes are active.
• Cluster is started on NODE1 and NODE2.
• Cluster Resource SAPHana_${SID}_${INSTNO} is configured with AUTOMATED_REGISTER=true.
• Check SAP HANA System Replication status:
  • Primary SAP HANA database is running on NODE1
  • Secondary SAP HANA database is running on NODE2
  • SAP HANA System Replication is activated and in sync
  • Secondary virtual IP address is active on NODE2

Test4 - Test procedure

Move SAP HANA primary to other node by using the pcs resource move command.

On a cluster node, run the following command.

pcs resource move SAPHana_${SID}_${INSTNO}-clone

Sample output:

# pcs resource move SAPHana_H4S_00-clone
Warning: Creating location constraint 'cli-ban-SAPHana_H4S_00-clone-on-cl-hdb-1' with a score of -INFINITY for resource SAPHana_H4S_00-clone on cl-hdb-1.
        This will prevent SAPHana_H4S_00-clone from running on cl-hdb-1 until the constraint is removed
        This will be the case even if cl-hdb-1 is the last node in the cluster

Test4 - Expected behavior

• The cluster creates location constraints to move the resource.
• The cluster triggers a takeover to the secondary SAP HANA database.
• The secondary virtual IP address stays on NODE2.
• If an application, such as SAP NetWeaver, is connected to a tenant database of SAP HANA, the application automatically reconnects to the new primary.

Test4 - Recovery procedure

The automatically created location constraints must be removed to allow automatic failover in the future.

Wait until the primary SAP HANA instance is active and remove the constraints.

On a cluster node, run the following command.

pcs constraint

pcs resource clear SAPHana_${SID}_${INSTNO}-clone

pcs constraint

The cluster registers and starts the SAP HANA database as a new secondary instance. After system replication status is in sync again (SOK), the cluster moves the secondary virtual IP address to NODE1.

pcs status --full