A new approach for replicating tables across different SAP HANA systems
Update on January 21, 2021: Hybrid scenario with Cloud Connector
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 2.0 SPS05 or later to SAP HANA Cloud as a hybrid scenario. For this hybrid scenario, Cloud Connector is needed to create a SDA remote sources to SAP HANA 2.0 SPS05 or higher which is protected by a firewall. Please also find further details in the below SAP Note regarding SDA supported remote sources.
As it requires SAP HANA 2.0 SPS05 or later when the source system is SAP HANA platform edition, please also see the below table as the supported scenarios.
|Replication between SAP HANA Cloud systems||Yes|
|Replication from SAP HANA 2.0 SPS05 or later (source) to SAP HANA Cloud (target)||Yes|
|Replication from SAP HANA 2.0 SPS04 or earlier (source) to SAP HANA Cloud (target)||No|
|Replication from SAP HANA Cloud (source) to SAP HANA 2.0 SPS05 or earlier (target)||No|
|Replication between SAP HANA 2.0 SPS05 or earlier systems||No|
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.
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
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.
For other limitations, please refer to the below.
- The following data types are not supported with this new remote table replication
- ALAPHNUM (not available in SAP HANA Cloud)
- ST_GEOMETRY and ST_POINT
- TEXT, BINTEXT, SHORTEXT (not available in SAP HANA Cloud)
- CLOB (available but only as an alias of NCLOB in SAP HANA Cloud)
- Full-text index is not supported (not available in SAP HANA Cloud)
- It cannot be activated on other SAP HANA native replication activated tables in the same time
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.