Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Say Hello World with Remote Data Sync Service on HANA Cloud Platform

Former Member
‎03-01-2016 10:19 AM

A common architecture pattern for enterprise grade IoT applications is the Media-Gateway-Controller (MGC). In MGC there are multiple devices (sensors/agents/devices) which can be visualized as edge of a network and generate data. This data is stored and processed in a central core (server + database).Then applications are built that can access this stored data to generate business analytics or trigger a business process.  The benefits of implementing this pattern for IoT application is that the data storage and processing is done at a central server which can be designed to have higher computing power, better security compared to devices and can be connected to existing business processes.

However, common challenges while implementing IoT applications are –

  • Availability of the central core that can handle data at high volumes and has high availability,
  • Lack of reliable network connectivity between devices and core especially for remote locations,
  • Limited storage and computing power at device level.

Keeping these challenges in mind, SAP HANA Cloud Platform provides 2 key services – IoT Service and Remote Data Sync Service. Take a look at Rui’s blog series to see how an IoT scenario can be developed using a Raspberry Pi and HANA Cloud Platform IoT services.

In this blog post, let’s look at HANA Cloud Platform Remote Data Sync Service.

Table of Contents

What is Remote Data Sync?

Looking into HANA Cloud Platform release notes for 18th June 2015, we see that Remote Data Sync Service is the new name for Mobilink service on HANA Cloud Platform. In short, MobiLink is a session-based synchronization technology designed to synchronize UltraLite and SQL Anywhere remote databases with a consolidated database. Mobilink synchronization is setup in two parts- a Mobilink client that connects to an instance of SQL Anywhere (aka SQLA) on a remote device and Mobilink server that connects to a consolidated database. The client-server combination takes care of keeping data in the databases in sync. The below picture shows how the synchronization works.

[Source: http://wiki.scn.sap.com/wiki/display/SQLANY/MobiLink]

Remote Data Sync (RD Sync) service acts as the Mobilink server on HANA Cloud Platform side. The benefit of this synchronization technology is that it allows users to store data in devices locally (on the device) and trigger or schedule synchronization with a consolidated database. This enables an occasionally connected scenario for IoT use-cases where the device does not have connectivity to cloud all the time. In this blog, I will show how to configure a SQLA database, RD sync service and setup synchronization for a simple Hello World use-case. In this example your local system is configured as a remote device which synchronizes data with HANA database in HANA Cloud Platform account. Currently RD sync service is available only for productive accounts and not for trial accounts. To get access to a productive HANA Cloud Platform account check here.

Prerequisites

  1. A productive HANA Cloud Platform account
  2. HANA instance configured for the account
  3. Enable Remote Data Sync Service on account
  4. Install SAP SQL Anywhere Remote Database Client version 16 on your local system.
    1. For productive development, you need a licensed version of SAP SQL Anywhere Remote Database Client.
    2. Link for free developer license: https://global.sap.com/campaign/ne/sybase/sql_anywhere_16_download_program/index.epx.
  5. Download HANA Cloud Platform SDK for Java EE 6 Web Profile from here: SAP Development Tools for Eclipse. Extract the zip file to a location on your local system. It is recommended to download and extract the latest version.

Steps 1 and 2 can be done using Eclipse based development tools or using Web IDE. I have given the Eclipse approach but the choice is yours. If you have not installed Eclipse based development tools for HANA Cloud Platform development, you can do it by following steps mentioned here: SAP Development Tools for Eclipse

Step 1: Create a database user for synchronization

Note: You need to have Database Administrator permissions to complete this step. Follow steps in documentation to create this user or contact your database administrator.

Open Eclipse and switch to “SAP HANA Administration Console perspective”. In Systems view add a new Cloud system. The user that is used for login to the cloud system must have necessary privilege to create a new database user on the HANA database.

Expand the “Security”->”Users” node. Create a new user with name “MOBILINK” and provide a password. Save the changes.  Note: You can give any name you like, I have used MOBILINK for this exercise.

Read SAP HANA Cloud documentation in case you face issues in this step.

Step 2: Create Hello World table in HANA

In Eclipse Systems view, add cloud system again with the newly created database user – MOBILINK. Note: You have to use the same HANA Cloud Platform account user and password remains the same.

Expand Catalog node to view schemas, tables, etc. Open SQL console for your cloud system. Create a Hello World table using this SQL:



CREATE TABLE HELLO_WORLD ( PKEY BIGINT NOT NULL, FIRST_NAME VARCHAR (10) DEFAULT ‘’ NOT NULL, LAST_NAME VARCHAR (10) DEFAULT ‘’ NOT NULL, PRIMARY KEY ( PKEY ) );

commit;

Once you execute this statement, refresh the systems view and check if the table is created.

In this example I have created the table in default schema hence I do not have to mention schema name in the steps below. In case you are using a different schema, adjust the scripts accordingly. I would recommend to follow the steps as they are and leave the extra spice for later once you have the basic synchronization up and running.

Step 3: Deploy Mobilink Server application

Mobilink server is deployed as a java application on HANA Cloud Platform account using console commands. The service needs to be enabled as a prerequisite so that the server can start successfully.

Open command prompt and execute these commands one by one. If needed set proxy host and password before executing these commands.


Important note: these settings like proxy, password are limited to current session of command prompt. If you close the command prompt, you have to execute the set commands again.  



set HTTP_PROXY_HOST=

set HTTP_PROXY_PORT=

set HTTPS_PROXY_HOST=

set HTTPS_PROXY_PORT=

set HTTP_NON_PROXY_HOSTS="localhost"

set NO_PROXY=localhost

set HTTP_PROXY=

set HTTPS_PROXY=

Set parameters like host name, user name, account name and passwords correctly. Setting these parameters avoids the trouble of having to type the parameters for each command.



set hcppass=

set dbpass=

set hcpaccount=

set hcpuser=

set dbuser=MOBILINK

set hcphost=

set hanainstancename=

Inside command prompt, switch to folder in which you have extracted your SDK for HANA Cloud Platform. If your SDK is in C:\ drive then switch to C:\neo-javaee6-wp-sdk-\tools. Execute these commands to deploy and start Mobilink server.



neo.bat deploy -h %hcphost% -a %hcpaccount% -b mobilinkserver -u %hcpuser% -s EMPTY_SITE --runtime mobilink --ev ML_ARGS="-zf -v" -p %hcppass%

neo.bat bind-db -h %hcphost% -a %hcpaccount% -b mobilinkserver -u %hcpuser% -i %hanainstancename% --db-user %dbuser% --db-password %dbpass% -p %hcppass%

neo.bat start -h %hcphost% -a %hcpaccount% -b mobilinkserver -u %hcpuser% -p %hcppass%

This will deploy and start your Mobilink server application. Open cockpit for your HANA Cloud Platform account and navigate to Java applications. Wait for the application to start.

Note: If the Mobilink server is configured and started correctly, you will see a number of tables starting with ML_ prefix in your HANA system. If you do not see them, check if your application is started properly. If not deploy the application and start it again.

Step 4: Create synchronization scripts in HANA

In SQL console for your cloud system, execute following procedures. These procedures are created when the RD Sync server starts.



call ml_add_table_script('v1','HELLO_WORLD','upload_insert','');

call ml_add_table_script('v1','HELLO_WORLD','upload_insert', 'insert into HELLO_WORLD (PKEY, FIRST_NAME, LAST_NAME) values ({ml r.pkey}, {ml r.first_name}, {ml r.last_name})');

call ml_add_user ('TEST_USER', '','');

commit;

If you look at these statements, you will see that there is an INSERT INTO SQL statement. In this I have mapped columns from our HELLO_WORLD table to some variables like {ml r.pkey} and so on. While writing these scripts may look cumbersome, it puts enormous power into the hands of those setting up the synchronization. Essentially what you are doing is declaring to RD Sync, data from which column in remote database goes into which column of consolidated database.

We are implementing only upload from remote database to consolidated hence we need to call only ‘upload_insert’ procedure. For download and bi-directional transfer we need similar scripts.

Step 5: Setup remote synchronization

In a productive setup, this needs to be done on a remote system that can access all remote databases. For now, we will treat our local system as a remote database and also do this setup on the same system.

Follow steps 1 to 6 as mentioned in help documentation for Developing Client-Initiated Synchronizati...

Step 2 is optional.

In step 7 for help documentation, select Synchronization type as Upload-only.

These steps are missing from the documentation but I found that the synchronization gives errors if we don't do them:

  1. In Sybase Central, double-click Synchronization Profiles.
  2. Select hello_world_sync_profile, and from its context menu, choose Properties.
  3. Go to the Extended Options tab.
  4. Set ScriptVersion property value to v1
  5. Choose Ok.

Step 6: Check if the synchronization works!

Open SQL Console for HANA system and execute “Select * from HELLO_WORLD”. This will show if data is synchronized for our HELLO_WORLD table. In case you do not see the rows that you inserted into the remote database (in step 4), check if you have followed all steps in correct order. Note: All the rows inserted into tables in remote database after the synchronization profile are recorded for copying to consolidated database. Any pre-existing content is not copied (I found this after some trial and error).

In addition to the basic upload only synchronization, you can implement a bi-directional synchronization. You can also configure automatic synchronization based on certain time interval, and synchronize data based on certain logic instead of the blind copy. I will try to cover these in a next blog. Hope you find this useful.

9 Comments
Labels in this area
Popular Blog Posts