The aim of this blog is to show how to do real-time replication from one SAP HANA Cloud instance to another. With applying of the latest development technologies, I will build an interesting scenario that will integrate between two cloud instances in such a way that when the data in the original table located in HANA Cloud instance B is changed, the change is automatically reflected in the replicated table located in a remote HANA Cloud instance A without any additional effort.

Prerequisites

For this scenario are needed two different instances of SAP HANA Cloud and for one of them, SAP Business Application Studio for Development should be Set Up.

• Create two SAP HANA Cloud instances in one or two different (trial account has a limit of one SAP HANA Cloud instance per account) global accounts: HANA_A and HANA_B, following the tutorial Deploy SAP HANA Cloud.
• At least for one of the accounts Set Up SAP Business Application Studio for Development (for my scenario this is the account where HANA_A is located)

Goal

To establish successful integration and real-time replication from one SAP HANA Cloud instance to another, which means when the data in the original table, located in instance A is changed, the change is automatically reflected in a replicated table located in a remote instance B without any additional effort.

Set up the development environment

Once the two instances are created, make sure they are started and running (especially if they were created earlier)

Go to ‘Manage SAP HANA Cloud‘ to check if the instance is running, if not, you can run it by pressing ‘Start’ from the menu which appears by clicking ‘More’ (the three dots in the last column of Actions)

The starting will take some time and when is done you can see the status ‘Running’ with green color.

For the instance HANA_B, open the Database Explorer, to create a table and insert some data.

Create a table and insert some data

In the Database Explorer of the HANA_B instance, you can log in with the DBADMIN and create schema, table and insert some data into the table.

CREATE SCHEMA MYDATA;
SET SCHEMA MYDATA;

CREATE COLUMN TABLE HOTEL (
    HNO INTEGER NOT NULL,
	NAME VARCHAR(50) NOT NULL,
    ZIP VARCHAR(5),
    ADDRESS VARCHAR(40) NOT NULL,
    PRIMARY KEY (HNO)
);

INSERT INTO HOTEL VALUES (10, 'Long Island', '11788', '1499 Grove Street');
INSERT INTO HOTEL VALUES (30, 'Lake Michigan', '60601', '354 Oak Terrace');
INSERT INTO HOTEL VALUES (100, 'Beachside', '32018', '1980 34th St.');
INSERT INTO HOTEL VALUES (110, 'Atlantic', '33441', '111 78th St.');
INSERT INTO HOTEL VALUES (120, 'Calypso', '90804', '35 Broadway');
INSERT INTO HOTEL VALUES (130, 'Evening Star', '90029', '13 Beechwood Place');
INSERT INTO HOTEL VALUES (140, 'Steamboat Louis Armstrong', '70112', '788 Main St.');

Once the table is created, it should look like this:

Set up the instances to be able to see data from the remote instance

1.   Create a technical user in the instance HANA_B, to log in with it from the remote instance HANA_A.

CREATE USER MYUSER 
    PASSWORD <password> 
    NO FORCE_FIRST_PASSWORD_CHANGE
    SET USERGROUP DEFAULT;

GRANT SELECT ON SCHEMA MYDATA TO MYUSER;

To test the user and the grant, add the database HANA_B in the Database explorer and log in with the user  MYUSER,

the newly created database will be added to the list. Right-click on it and ‘Open SQL Console‘

then if the select on the table works, it means the user and the grant work.

SELECT * FROM MYDATA.HOTEL; 

2.   Set the allowed access of the instance where the data is located, HANA_B

• We can set ‘Allow all IP addresses’ (not recommended for production environment) or
• We can set ‘Allow specific IP addresses and IP ranges’ if we want to have a more secure and protected system.

From the ‘SAP HANA Cloud ‘overview of the ‘SAP BTP Cockpit’ you can open ‘SAP HANA Cloud Central’ by clicking ‘Manage SAP HANA Cloud’ button in the right upper corner.

From ‘SAP HANA Cloud Central’ by clicking on ‘More’ button of your HANA instance, then ‘Manage Configuration’

then ‘Edit’ button in the right upper corner, you can edit the instance’s configuration and then under the ‘Connections’ tab you can change the ‘Allowed connections’ setting.

For this scenario, I want to allow access from HANA_A to HANA_B, which means to set ‘Allow specific IP addresses and IP ranges’ and then specify the IP address of HANA_A. The IP Address of one HANA instance you can check by coping the EndpointAnd in the command line type nslookup and paste the endpoint, taking out the port of the end, and you should see the answer of what the IP address should be. In the ‘Manage Configuration’ of HANA_B we can now specify the IP address of HANA_A using CIDR notation.

3.     Add a certificate

Open SAP HANA Cockpit of HANA_A and log in as DBADMINFirst navigate to the ‘Security and User Management’ section and then to ‘Certificate Store’

To download the root certificate, you can go directly to the link: https://dl.cacerts.digicert.com/DigiCertGlobalRootCA.crt.pem (*source)

And then ‘Import’ and ‘Browse’ the file which you downloaded, you can see this is the DigiCert Global Root certificate, then add it to a personal security environment, called certificate collection. Next ‘Go to Certificate Collection’ (the blue button) and ‘Add Collection’ (the blue button), give any name you like and then add the DigiCert Global Root certificate to it edit the purpose of the certificate collection, it should be ‘REMOTE SOURCE’ and that’s it.

4.     Create a remote source to test the access

From the SAP HANA Database Explorer of HANA_A go to ‘Catalog’ and then right click on ‘Remote Source’ and ‘Add Remote Source’

You can choose the Name you want, and then specify how to connect, so you are going to use HANA(ODBC). Next, specify the ‘Server’ and for that, you need to go again to the HANA_B instance and copy its Endpoint, paste and remove the port and the semicolon on the end and add the port on the line that follows. Choose the ‘DML Mode’ you need. In the ‘Extra Adapter Properties’ you must specify encrypt=true. For the Credentials, I am going to use the Technical User which I already created in the HANA_B instance in the beginning. Once it is created if you can see the list of the schemas, then all is set up correctly, if not, something is missing then please check again the allowance and the certificate again.

Connect to the Cloud Foundry and create a new project in the SAP Business Application Studio

In this part, you will create a project and replication task in it, but before starting, let’s first log in to the cloud foundry from the SAP Business Application Studio of the account where the instance HANA_A is located. Once you set up the SAP Business Application Studio and created Dev space in it, the next step is to log in to the cloud foundry. Find the cloud foundry icon on the left side menu and then click login, copy the Endpoint of HANA_A (from SAP BTP Cockpit) and provide the username and password. After successful login, create a new project from template and choose SAP HANA Database Project. Choose any project name you want and proceed to the next steps:

Create User Provided Service

You need User Provided Service to enable your HDI container to access the objects within your classical schema in Hana Cloud, or in this case, the remote source HANA_B_REMOTE.

1.      Create a user in the HANA_A classical schema and grant the required roles to it.

Go back to the SAP Database Explorer of the HANA_A and create the user:

CREATE USER DEVUSER PASSWORD Password1 NO 
FORCE_FIRST_PASSWORD_CHANGE SET USERGROUP DEFAULT;
GRANT CATALOG READ TO DEVUSER WITH ADMIN OPTION;
GRANT CREATE SCHEMA TO DEVUSER WITH ADMIN OPTION;
GRANT CREATE REMOTE SOURCE TO DEVUSER WITH ADMIN OPTION;

2.      Create the User Provider Service

In the SAP BTP Cockpit navigate to the Services/Instances and in the right upper corner next to the Create button, click the arrow down and then ‘User-Provided Service Instance’Provide the instance name and specify the parameters in JSON format:

{
  	   "user": "DEVUSER",
      "password": "Password1",
      "schema": "DEVUSER",
      "tags": [
          "hana"
      ]
}

3.      Create the Remote Source

Once you create the DEVUSER, log in with it and create Remote Source pointing to HANA_B

4.      Configuration within the HDI container

• Adjust the mta.yaml, declaring the user provided service you created before, and deploy (or Build in WebIDE) the project to be sure that everything works correct.
• Create the specific grants that we want to access within our XS classic schema

  {
      "grant-service":{
         "object_owner":{
            "global_object_privileges":[
               {
                  "name":"HANA_B_REMOTE",
                  "type":"REMOTE SOURCE",
                  "privileges":[
                     "CREATE VIRTUAL TABLE",
                     "CREATE VIRTUAL FUNCTION",
                     "CREATE REMOTE SUBSCRIPTION",
                     "PROCESS REMOTE SUBSCRIPTION EXCEPTION",
                     "ALTER",
                     "DROP"
                  ]
               }
            ]
         },
         "application_user":{
            "global_object_privileges":[
               {
                  "name":"HANA_B_REMOTE",
                  "type":"REMOTE SOURCE",
                  "privileges":[
                     "CREATE VIRTUAL TABLE",
                     "CREATE VIRTUAL FUNCTION",
                     "CREATE REMOTE SUBSCRIPTION",
                     "PROCESS REMOTE SUBSCRIPTION EXCEPTION",
                     "ALTER",
                     "DROP"
                  ]
               }
            ]
         }
      }
   }
  

Create Replication Task

Open the ‘Command Palette’and type >Create SAP HANA Database Artifact Next, connect to the remote source, then select the object that you want to replicate. First click on the + ‘Add Object’, and search for our object, select it and scroll down You can choose any prefixes for the source and target tables, and the replication mode. For this scenario please choose ‘Initial + realtime’ which will provide the initial load, but also refresh the data, any time change happened. Once this is done, you need to deploy this object. This actually is going to build a lot of different objects, if you check the table list in the HDI container you can see the source and the target tables. Before executing the replication task, you can see that the target table is empty, and the source table is full. To execute the replication task you can search in the procedure list, generate the CALL Statement and just call the procedure. With this, replicating is happening. If you go back now to the TARGET_HOTEL table, you can see the data now.

Test the integration

To test the real-time replication, you can run an update on the target table on the source system and see if is replicated in the remote instance.

First, go back to HANA_B and run an update:

set schema MYDATA;
update HOTEL set ADDRESS = '77 Broadway' where HNO = 120;
select * from HOTEL order by HNO;

Now, without doing anything, if you go back and refresh TARGET_HOTEL you can see that the update is already there

You can of course perform more tests by inserting or deleting data.

Conclusion

With this scenario, real-time data from a table in remote HANA Cloud is successfully replicated, but with the whole scenario, you can see how to access data from one HANA Cloud instance to another and how to access data from the HDI container to the HANA Cloud instance. Most important, the scenario is built one time and the replicated data is real-time forever.

If you are interested in the topics of HANA Cloud, you can find very nice content in the SAP HANA Academy:  SAP HANA Cloud

If you like the approach, of building an interesting scenario by applying the various possibilities offered by Hana, follow me for similar content.