Configuring SAP HANA Cost-Optimized Scale-Up System Replication in a RHEL HA Add-On Cluster

Creating a database user in the SAP HANA production database

Use the following steps to create a database user in the SAP HANA production database.

1. Create a database user in the SystemDB of the SAP HANA production system, or provide credentials of an existing user. The hook script uses this database user to connect to the production database and alter the configuration parameters.

   Log in to the SystemDB of the primary instance by using the SAP HANA database interactive terminal hdbsql or SAP HANA Cockpit, and create a new user.

   For example, connect to the database by using hdbsql in a terminal session.

   sudo -i -u ${sid}adm -- hdbsql -i ${INSTNO} -d SYSTEMDB -u SYSTEM
   

   Create a user.

   CREATE USER HA_HOOK PASSWORD <Password> no force_first_password_change;
   

   Grant the required privileges to the user.

   Grant privilege INIFILE ADMIN to allow for changes of profile parameters.

   GRANT INIFILE ADMIN TO HA_HOOK;
   

   Verify the HA_HOOK user.

    sudo -i -u ${sid}adm -- hdbsql -d SYSTEMDB -u SYSTEM select \* from users where user_name = \'HA_HOOK\';
   

2. Add the user credentials to the secure user store hdbuserstore.

   On both nodes, run the following command. Use the password that you set in the previous step.

   sudo -i -u ${sid}adm -- hdbuserstore SET HA_HOOK_KEY localhost:3${INSTNO}13 HA_HOOK <Password>
   

   Check the update to the hdbuserstore.

   sudo -i -u ${sid}adm -- hdbuserstore list
   

   On the primary instance, test the connection with the stored user key.

   sudo -i -u ${sid}adm  -- hdbsql -U HA_HOOK_KEY select \* from m_inifiles;
   

Creating the hook script

Python sample files for creating hook scripts are delivered as part of the SAP HANA installation. The samples are located in directory $DIR_INSTANCE/exe/python_support/hdb_ha_dr.

The target directory /hana/shared/myHooks was already created for hook SAPHanaSR.py. Create a HA/DR provider hook in /hana/shared/myHooks. The following hook script is based on the HADRdummy.pysample.

On NODE2, edit the file /hana/shared/myHooks/SAPHanaCostOptSR.py and add the following content.

"""
Sample for a HA/DR hook provider.

When using your own code in here, please copy this file to location on /hana/shared outside the HANA installation.
This file will be overwritten with each hdbupd call! To configure your own changed version of this file, please add
to your global.ini lines similar to this:

    [ha_dr_provider_<className>]
    provider = <className>
    path = /hana/shared/haHook
    execution_order = 1


For all hooks, 0 must be returned in case of success.
"""

from __future__ import absolute_import
from hdb_ha_dr.client import HADRBase, Helper
from hdbcli import dbapi
import os, time

class SAPHanaCostOptSR(HADRBase):

    def __init__(self, *args, **kwargs):
        # delegate construction to base class
        super(SAPHanaCostOptSR, self).__init__(*args, **kwargs)

    def about(self):
        return {"provider_company" :        "SAP",
                "provider_name" :           "SAPHanaCostOptSR", # provider name = class name
                "provider_description" :    "Handle reconfiguration event for cost-optimized system replication",
                "provider_version" :        "1.0"}

    def postTakeover(self, rc, **kwargs):
        """Post takeover hook."""

        # prepared SQL statements to remove memory allocation limit and pre-load of column tables
        stmnt1 = "ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') UNSET ('memorymanager','global_allocation_limit') WITH RECONFIGURE"
        stmnt2 = "ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') UNSET ('system_replication','preload_column_tables') WITH RECONFIGURE"

        myPort = int('3' + os.environ.get('DIR_INSTANCE')[-2:] + '15')
        myKey = self.config.get("userkey") if self.config.hasKey("userkey") else "HA_HOOK_KEY"

        self.tracer.info("%s.postTakeover method called with rc=%s" % (self.__class__.__name__, rc))
        self.tracer.info("%s.postTakeover method: userkey: %s, port: %s" % (self.__class__.__name__, myKey, myPort))

        if rc in (0, 1):
            # rc == 0: normal takeover succeeded
            # rc == 1: waiting for force takeover
            conn = dbapi.connect(userkey=myKey, address='localhost', port=myPort)
            self.tracer.info("%s: Connect using userkey %s - %s" % (self.__class__.__name__, myKey, conn.isconnected()))
            cursor = conn.cursor()
            rc1 = cursor.execute(stmnt1)
            self.tracer.info("%s: (%s) - %s" % (self.__class__.__name__, stmnt1, rc1))
            rc2 = cursor.execute(stmnt2)
            self.tracer.info("%s: (%s) - %s" % (self.__class__.__name__, stmnt2, rc2))
            return 0
        elif rc == 2:
            # rc == 2: error, something went wrong
            return 0

Activating the cost optimized hook

Use the following steps to activate the cost optimized hook.

1. Stop the cluster.

   On any cluster node, run the following command.

   pcs cluster stop --all
   

2. Set the file ownership of the hook script.

   On NODE2, run the following command.

   chown -R ${sid}adm:sapsys /hana/shared/myHooks
   

3. Update the global.ini configuration file on NODE2 to enable the hook script.

   On NODE2, run the following command to add the required parameters to the global.ini file.

   sudo -i -u ${sid}adm -- <<EOT
       python \$DIR_INSTANCE/exe/python_support/setParameter.py \
         -set SYSTEM/global.ini/ha_dr_provider_SAPHanaCostOptSR/provider=SAPHanaCostOptSR \
         -set SYSTEM/global.ini/ha_dr_provider_SAPHanaCostOptSR/path=/hana/shared/myHooks \
         -set SYSTEM/global.ini/ha_dr_provider_SAPHanaCostOptSR/userkey=HA_HOOK_KEY \
         -set SYSTEM/global.ini/ha_dr_provider_SAPHanaCostOptSR/execution_order=2 \
         -set SYSTEM/global.ini/trace/ha_dr_saphanacostoptsr=info
   EOT
   

4. Check the content of the global.ini file.

   cat /hana/shared/${SID}/global/hdb/custom/config/global.ini
   

5. Verify that the hook functions.

   • Restart the HANA instance on NODE2 and verify that the hook script works as expected.
   • Trigger the hook with an SAP HANA takeover operation.
   • Check whether the hook logged anything in the trace files.

   sudo -i -u ${sid}adm -- \
       sh -c 'grep SAPHanaCostOptSR $DIR_INSTANCE/$VTHOSTNAME/trace/nameserver_*.trc'
   

   After you verify that the hook functions, you can restart the HA cluster.

6. Start the HA cluster.

   On any cluster node, run the following command.

   pcs cluster start --all
   

   Check the status of the cluster.

   pcs status --full
   

Test1 - Description

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

Test1 - Prerequisites

• A functional two-node RHEL HA Add-On cluster for HANA system replication.
• Both cluster nodes are active.
• Cluster is started on NODE1 and NODE2.
• Cluster Resource SAPHana_${SID}_${INSTNO} is configured with AUTOMATED_REGISTER=false.
• Check SAP HANA System Replication status:
  • The primary SAP HANA database is running on NODE1.
  • The secondary SAP HANA database is running on NODE2.
  • HANA System Replication is activated and in sync.
• The secondary SAP HANA database on NODE2 is running with reduced memory configuration.
  • The global_allocation_limit is reduced.
  • Preload of column tables is disabled (preload_column_tables = false).
• A nonproduction SAP HANA system ${SID_NP}is running on NODE2.

Test1 - Test procedure

Crash SAP HANA primary by sending a SIGKILL signal as 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 HANA database and marks the resource as failed.
• The cluster promotes the secondary HANA database on NODE2 to take over as new primary.
  • The cluster stops the nonproduction database ${SID_NP} on NODE2.
  • During activation, the global_allocation_limit and preload_column_tables parameters are reset to default.
• The cluster releases the virtual IP address on NODE1, and acquires it on the new primary 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.

On NODE2, run the following commands to check that the global_allocation_limitand preload_column_tables are unset.

sudo -i -u ${sid}adm -- hdbcons "mm globallimit" | grep limit

grep -E "global_allocation_limit|preload_column_tables" \
     /hana/shared/${SID}/global/hdb/custom/config/global.ini

Test1 - Recovery procedure

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

On NODE1, run the following commands to register the previous primary as new secondary.

sudo -i -u ${sid}adm -- <<EOT
    hdbnsutil -sr_register \
        --name=${DC1} \
        --remoteHost=${NODE2} \
        --remoteInstance=${INSTNO} \
        --replicationMode=sync \
        --operationMode=logreplay \
        --online
EOT

Verify the system replication status.

sudo -i -u ${sid}adm -- <<EOT
    hdbnsutil -sr_state
    HDBSettings.sh systemReplicationStatus.py
EOT

On NODE1, run the following command to start the cluster node.

pcs cluster start

The new secondary instance restarts and shows up in status synced (SOK).

pcs status --full

Configure cluster resource SAPHana_${SID}_${INSTNO} with AUTOMATED_REGISTER=true.

On NODE1, run the following command.

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

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

Test2 - Description

Use cluster commands to move the primary instance back to the other node.

Test2 - Prerequisites

• A functional two-node RHEL HA Add-On cluster for HANA system replication.
• Both cluster 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:
  • The primary SAP HANA database is running on NODE2.
  • The secondary SAP HANA database is running on NODE1.
  • HANA System Replication is activated and in sync.
• The nonproduction SAP HANA system ${SID_NP}is stopped on NODE2.

Test2 - Test Preparation

Unmanage the cluster resource for the nonproduction SAP HANA system to prevent that it starts when the memory resources of the secondary are not restricted.

On NODE1, run the following command.

pcs resource unmanage group_${sid_np}_non_prod

Test2 - Test Procedure

On NODE1, run the following command to move the SAP HANA primary back to NODE1.

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

Test2 - Expected behavior

• The cluster creates a location constraint to move the resource.
• The cluster triggers a takeover to the secondary HANA database 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.
• The resource of the nonproduction SAP HANA system ${SID_NP} is in the unmanaged state and isn't started automatically.

Test2 - Recovery procedure

Several steps need to be followed to reestablish the complete HA scenario.

1. Wait until the primary HANA instance is active. Then, reduce the memory footprint of the secondary.

   On NODE2, run the following commands to reduce the memory.

   export GLOBAL_ALLOCATION_LIMIT=<size_in_mb_for_hana_secondary>
   

   sudo -i -u ${sid}adm -- <<EOT
       python \$DIR_INSTANCE/exe/python_support/setParameter.py \
         -set SYSTEM/global.ini/system_replication/preload_column_tables=false \
         -set SYSTEM/global.ini/memorymanager/global_allocation_limit=$GLOBAL_ALLOCATION_LIMIT
   EOT
   

2. Remove the location constraint, which triggers the start of the secondary instance.

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

   Verify that the constraint is cleared.

   pcs constraint
   

   Check the cluster status.

   pcs status --full
   

3. On NODE2, run the following commands to check that the global_allocation_limitand preload_column_tables are set.

   sudo -i -u ${sid}adm -- hdbcons "mm globallimit" | grep limit
   

   grep -E "global_allocation_limit|preload_column_tables" \
        /hana/shared/${SID}/global/hdb/custom/config/global.ini
   

4. Reactivate the resource for the nonproduction SAP HANA system.

   On NODE2, run the following command.

   pcs resource manage group_${sid_np}_non_prod
   

   The resource of the nonproduction SAP HANA system ${SID_NP} is managed and the nonproduction system starts on NODE2.

   pcs status --full
   

Test3 - Description

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

Test3 - Prerequisites

• A functional two-node RHEL HA Add-On cluster for HANA system replication.
• Both cluster 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:
  • The primary SAP HANA database is running on NODE1.
  • The secondary SAP HANA database is running on NODE2.
  • HANA System Replication is activated and in sync.
• The secondary SAP HANA database on NODE2 is running with reduced memory configuration.
  • The global_allocation_limit is reduced.
  • Preload of column tables is disabled (preload_column_tables = false).
• A nonproduction SAP HANA system ${SID_NP}is running on NODE2.

Test3 - Test procedure

Crash primary on NODE1 by sending a shutoff system request.

On NODE1, run the following command.

sync; echo o > /proc/sysrq-trigger

Test3 - Expected behavior

• NODE1 shuts down.
• The cluster detects the failed node and sets its state to OFFLINE.
• The cluster promotes the secondary HANA database on NODE2 to take over as new primary.
  • The cluster stops the nonproduction database ${SID_NP} on NODE2.
  • During activation, the global_allocation_limit and preload_column_tables parameters of SAP HANA ${SID}are reset.
• The cluster acquires the virtual IP address on NODE2 on the new primary.
• If an application, such as SAP NetWeaver, is connected to a tenant database of SAP HANA, the application automatically reconnects to the new primary.

Test3 - Recovery procedure

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

On NODE1, run the following command.

pcs cluster start

pcs status --full

As cluster resource SAPHana_${SID}_${INSTNO} is configured with AUTOMATED_REGISTER=true, SAP HANA restarts when NODE1 joins the cluster and the former primary is registered as secondary.

Then, rerun the steps in Test2 - Test the manual move of SAPHana resource to another node to revert to the initial situation.

Test4 - Description

Simulate a crash of the secondary HANA database.

Test4 - Prerequisites

• A functional two-node RHEL HA Add-On cluster for HANA system replication.
• Both nodes 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:
  • The primary SAP HANA database is running on NODE1.
  • The secondary SAP HANA database is running on NODE2.
  • HANA System Replication is activated and sync.

Test4 - Test Procedure

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

On NODE2, run the following command.

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

Test4 - Expected behavior

• SAP HANA secondary on NODE2 crashes.
• The cluster detects the stopped secondary HANA database and marks the resource as failed.
• The cluster restarts the secondary HANA database.
• The cluster detects that the system replication is in sync again.

Test4 - Recovery Procedure

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

On NODE2, run the following command.

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

pcs status --full