Skip to Content

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.

Mobilink.png

[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 Synchronization.

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.

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Jagan Sundaramoorthy

    Very nice blog and it is really useful. I have one question regarding the script in your blog:

     

    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})’);


    Can you please let me know what is ml r ?

    Where is this defined? is it keyword or do we need to set this some where in RDS server and client?


    Thank you.


    (0) 
  2. Jagan Sundaramoorthy

    Thank you Murali.

     

    I have one more clarification:

     

    Once the data is pushed to the consolidated database, is there a posibility of configuring automatic/periodic delete/remove of data in SQLAnywhere.

     

    Thank you.

    (0) 
    1. Murali Shanmugham

      Hi Jagan, That’s a good question.

       

      I had a chat with few of my colleagues and can say that you need to handle this both from transaction log files as well as your database files in SQL Anywhere.

       

      For transaction log files, you can use dbmlsync command to keep your log files in small sizes and use the command DELETE_OLD_LOGS to delete old log files which have been synchronized.You can also use delete_old_logs command to keep the logs for a certain number of days. To limit the size of the database file, you can issue an SQL command START AND STOP SYNCHRONIZATION DELETE.

      (0) 
  3. Mahendra Bhandari

    Not sure whether this will right place or not but whilst trying to run “neo.bat deploy -h %hcphost% -a %hcpaccount% -b mobilinkserver -u %hcpuser% -s EMPTY_SITE –runtime mobilink –ev ML_ARGS=”-zf -v” -p %hcppass%” from /~/neo-javaee6-wp-sdk-\tools…… I’m getting an error as below-

     

    Deployment finished with error

     

     

    ERROR: Runtime “mobilink” is not supported.

    Available runtimes :

      neo-javaee6-wp

      neo-java-web

     

     

    If you need help, provide the output of the command and attach the log directory

    [C:\Users\<user>\Downloads\Neo\tools\log]

     

    Any idea on this?

     

    FYI- I have activated the Remote Data Sync Services in my HCP Admin, Downloaded the Neo Client, SQL Anywhere et al required thingy but error is as above.

     

    Do i need to download some other version of SAP HANA Cloud platform Console Client (aka Neo Client)? Or is it something not related to neo?

     

    Thanking you in advance!

    ~ Mahendra

    (0) 
    1. Murali Shanmugham

      Hi Mahendra, Can I please request you to open a discussion in this space and refer to this blog. You might get more responses and it would be easy to search for others too.

       

      Thanks,

      Murali.

      (0) 

Leave a Reply