A new approach for replicating tables across different SAP HANA systems

Starting with SAP HANA Cloud, SAP HANA supports a new remote table replication feature for replicating tables across different SAP HANA systems natively. With this new feature, users can handily replicate remote tables as it directly sends DDL and DML logs from a source system to a target system through SAP HANA smart data access (SDA) ODBC connection.In a nutshell, the new remote table replication was developed with the following five key characteristics.

• Transient (in-memory) log-based replication: As this new feature is log-based in transactional layer, it can provide better performance with less overhead to a source system compared to trigger-based replication
• ODBC connection: It ensures optimized performance between HANA systems because it uses SDA ODBC connection with hanaodbc adapter for its log/data transfer
• Transactional consistency: If multiple source tables are updated within one transaction, it applies the same behavior on the target tables to ensure database level transactional consistency (transactional consistency is only guaranteed if the transaction is handled on database level, and it doesn’t guarantee the transaction consistency on application level like ABAP)
• Near real-time replication: It keeps replicating when changes happen (change data capture)
• Auto reactivation for failures: When an indexserver is turned off for any reasons such as crash or upgrade, all replications are disabled but when the indexserver restarts, those are reactivated and resumes replicating

Getting started with the new remote table replication

First of all, this new feature is supported between SAP HANA Cloud systems. And it is also supported when replicating tables from SAP HANA Platform 2.0 SPS05 or later (source) to SAP HANA Cloud (target) as a hybrid scenario. For this hybrid scenario, Cloud Connector is needed to create a SDA remote sources to SAP HANA Platform 2.0 SPS05 or higher which is protected by a firewall.

Please also see the below table as the supported scenarios.

That is, this remote table replication feature is only supported when the target is SAP HANA Cloud as this is available in SAP HANA Cloud exclusively. So, it cannot be used for replicating tables between SAP HANA Platform 2.0 (on-premise) systems. For replicating tables between SAP HANA Platform 2.0 (on-premise) systems, SAP HANA smart data integration (SDI) is the right solution.

Configuration and activation

In order to use this new remote table replication, the first step is creating a remote source with SDA in the target SAP HANA Cloud. SDA supports 3 types of credential modes to access a remote source with SAP HANA Cloud: technical user, secondary credentials, and single sign-on (SSO) with JSON Web Tokens (JWT). Currently, this new feature only supports the technical user mode, and users need to configure ‘DML Mode’ of a remote source property as ‘readwrite’.

The ‘readwrite’ is a default value for the remote source property ‘DML Mode’ in SAP HANA Database Explorer but please make sure that the created remote source is configured correctly. And the remote source should be named in UPPERCASE to initialize the remote table replication.

For more details of creating an SAP HANA Cloud remote source, please see the below official documentation.

SAP HANA Cloud Data Access Guide: Import Certificates for SSL Connections to Remote Sources
SAP HANA Cloud Data Access Guide: Create an SAP HANA Cloud Remote Source

For creating an SAP HANA on-premise remote source as the hybrid scenario which becomes ever more important, please see the below official documentation.

SAP HANA Cloud Data Access Guide: Set Up the Cloud Connector
SAP HANA Cloud Data Access Guide: Create an SAP HANA On-Premise Remote Source

The next step is creating a virtual table in the target system. The following statement will create a virtual table V_CUSTOMER of MARKETING schema in target system based on CUSTOMER table of SALES schema in source system.

CREATE VIRTUAL TABLE MARKETING.V_CUSTOMER AT "REMOTE_REP"."<NULL>"."SALES"."CUSTOMER";

Then creating a replica table and remote subscription is needed.

-- create a replica table
CREATE COLUMN TABLE MARKETING.R_CUSTOMER LIKE MARKETING.V_CUSTOMER;

-- create a remote subscription
CREATE REMOTE SUBSCRIPTION SUB ON MARKETING.V_CUSTOMER TARGET TABLE MARKETING.R_CUSTOMER;

As the replica table is created with COLUMN LOADABLE by default, the entire columns will be loaded into memory and it ensures the performance. However, if needed, the user can switch it to PAGE LOADABLE. It means that column data is loaded by page into the buffer cache and it can reduce the memory usage. For switching, the user can use the following statement.

-- switch to page loadable
ALTER TABLE MARKETING.R_CUSTOMER PAGE LOADABLE;

-- switch to column loadable
ALTER TABLE MARKETING.R_CUSTOMER COLUMN LOADABLE;

Those are all about the configuration for new remote table replication, and the following statements can be used for activating, deactivating, or dropping the replication.

-- activate the replication
ALTER REMOTE SUBSCRIPTION SUB DISTRIBUTE;

-- deactivate the replication
ALTER REMOTE SUBSCRIPTION SUB RESET;

-- drop the replication
DROP REMOTE SUBSCRIPTION SUB;

If users drop the replication and want to reactivate it, they need to truncate or recreate the replica table. Please find the further details in the below links.

SAP HANA Cloud Data Access Guide: Configure Remote Table Replication with the SDA HANA Adapter
SAP HANA Cloud Data Access Guide: System Views for Monitoring Remote Table Replication

Auto reactivation

As mentioned, this new remote table replication feature supports auto reactivation for failures. During the reactivation, it synchronizes each pair of source and target table first then changes their replication status to ENABLED. Users can also manually do global reactivation with the following statement.

ALTER SYSTEM ENABLE ALL TABLE REPLICAS USING REMOTE SOURCE ALL;

Differences from the replication with SDI or SLT

SAP HANA smart data integration (SDI) with HanaAdapter can be used for replicating tables between SAP HANA Cloud systems, but this new feature with SDA hanaodbc adapter provides better performance with log-based replication and simplified approach without deploying the Data Provisioning (DP) Agent.
However, even though it supports near real-time change data capture as SDI does, there are some limitations. For example, the schema of source table and target table must be identical with no additional filters or different partitions. If you would like to know further details of limitations or comparisons between SDI and SDA, please also refer to the below link.

SAP HANA Cloud Data Access Guide: Replicating Tables from Remote Sources

SAP Landscape Transformation (SLT) Replication Server can also be used for replicating tables between SAP HANA systems but it is trigger-based and requires additional installation. For these reasons, this new remote table replication can provides benefits from log-based replication with simplified approach compared to SLT.

Furthermore, the key differentiator compared to SDI and SLT is, as explained, in case multiple source tables are updated within one transaction, it applies the same behavior on the target tables to ensure database level transactional consistency. However, it doesn’t mean that SDI does not support transactional consistency. The difference is that this new remote table replication ensures transactional commit order based on original transaction commit ID.

Other limitations

For other limitations, please refer to the below link.

SAP HANA Cloud Data Access Guide: Unsupported Data Types and Other Limitations

Hybrid scenario and futures

As briefly mentioned above, the hybrid scenario is growing in importance and this new remote table replication will play a crucial role as one of key enablers for the hybrid scenario. In parallel, it will keep evolving and be augmented with more capabilities and better supportabilities. So, please stay tuned for our next blog series.