Introduction

Sometimes it would be great to have a second HANA database available to be able to create a remote source connection to it, to be able to do some tests for projects or to reproduce situations which are described here in the SAP Community.

As we have free options available to use a HANA database, namely a tenant database in HCP (https://blogs.sap.com/2016/01/13/sap-hana-multitenant-database-containers-mdc-scenarios-now-on-trial-landscape/) and SAP HANA Express Edition (http://www.sap.com/developer/topics/sap-hana-express.html), it is possible to setup different scenarios of HANA remote connections for free.

Following HANA remote source setups are described below:

• HCP HANA tenant database using the same HANA tenant database as remote source
• HANA Express installation using the same HANA Express installation as remote source
• HANA Express using HCP HANA tenant database as remote source via SAP HANA Cloud Connector

Preparations

For the scenarios below following prepartions are done.

Creation of HCP tenant database

First a HCP tenant database is created (like described in post https://blogs.sap.com/2016/01/13/sap-hana-multitenant-database-containers-mdc-scenarios-now-on-trial-landscape/).  For that test case the tenant database is named “pfefferfmdc” (the name is necessary later for setting up the connection from the HANA Express installation via the SAP HANA Cloud Connector).

A connection to the database is also added to a Eclipse installation using the SAP HANA Tools and SAP HANA Cloud Platform Tools.

Creation of a test schema

To have later an object to test via the remote source connection, a test schema “REMOTE_SRC_TEST” with a column table “REMOTE_TABLE_TEST” is created within the HCP HANA tenant database. The table contains some dummy values which are not relevant for the scenarios.

Creation of a user, used for the remote connection

Of course it would be possible to create the remote connection using the SYSTEM user, but for security and organization reasons an own dedicated user for a remote connection should be used. Therefore a test user “REMOTE_SRC_USER” is created in the HCP HANA tenant database. The user has privileges for the test schema “REMOTE_SRC_TEST”.

Installation of HANA Express

For the scenarios in which a remote source should be added to a HANA Express installation a HANA Express installation is necessary. All the required steps are described on site http://www.sap.com/developer/topics/sap-hana-express.html. For that test HANA Express 2 is used. The SystemDB ís added to the Eclipse installation too. So it can be used later to create the remote connection via the Eclipse based tooling.

In the HXE SystemDB the same test schema “REMOTE_SRC_TEST” with a table “REMOTE_TABLE_TEST” is set up. Also the test user “REMOTE_SRC_USER” is created.

Scenarios

HCP HANA tenant database using the same HANA tenant database as remote source

The easiest way to use a HANA database as a remote database is to use the same HCP HANA tenant database. You do not have to setup a second database in that case.

First the IP and port for the tenant database has to be found out. For that the “Hosts used to connect” properties have to be checked in the Eclipse tooling (to open the dialog the conext menu entry “Properties” has to be called for the connection to the tenant database in Eclipse).

For the test the Host Name/IP is 10.117.96.49 and the port is 30056. It has to be considered that the IP cannot be reached directly from outside the SAP network, but as the remote source is created from the HCP HANA tenant database itself in the SAP network, it can be reached.

The next steps show the setup of the remote source using the IP and port information obtained. It has to be considered that the Adapter Name must be set to “HANA (ODBC)”.

After the successful connection the test schema and test table can be seen via the remote source connection. A virtual table can be created for the remote source table.

HANA Express installation using the same HANA Express installation as remote source

The next scenario is to use a HANA Express database as remote source in the same HANA Express database. The procedure to setup the scenario is the same than for the first scenario. As hostname/IP of the HANA Express installation “hxehost” or the IP has to be used. In that test a connection is done to the SystemDB which is reachable at port 39013 in a HXE 2 installation.

The remote source has to be created using the HXE specific properties.

After the connection is successfully established, the test schema and test table can be seen via the remote source.

HANA Express using HCP HANA tenant database as remote source via SAP HANA Cloud Connector

In this last scenario it is described how the HCP HANA tenant database is used as remote source in a HANA Express installation.

Now someone may say, that it is the same than the first scenario, just using the IP/port found out for the HCP HANA tenant database. But as already said, the IP cannot be reached outside the SAP network directly. Therefore no direct connection is possible. The solution to establish the connection is, to use service channels offered by the SAP HANA Cloud Connector.

First the SAP HANA Cloud Connector has to be installed (https://help.hana.ondemand.com/help/frameset.htm?57ae3d62f63440f7952e57bfcef948d3.html) and configured (https://help.hana.ondemand.com/help/frameset.htm?db9170a7d97610148537d5a84bf79ba2.html). After that steps the SAP HANA Cloud Connector is up an running and connected to the HCP account which offers the HCP HANA tenant database.

Next step is to add a service channel for the HCP HANA tenant database (in that test case the database is named “pfefferfmdc”) in the SAP HANA Cloud Connector section “On-Premise to Cloud”. With that a JDBC and ODBC connection can be established to the database via the SAP HANA Cloud Connectors hostname/IP and the defined port. The port is 35115 in that scenario.

After the setup of the SAP HANA Cloud Connector service channel the steps to establish the remote source connection are the same than in the scenarios before.

First the remote source connection has to be created using the hostname/IP and port of the SAP HANA Cloud Connector instance (in that test case the IP of the SAP HANA Cloud Connector is 192.168.100.90).

After the connection is established the test schema and test table can be accessed from the HANA Express installation via the remote source.

If there are some issues to connect to the HCP HANA tenant database via the service channel in the remote source, the network settings have to be checked. It can be that the access from the HANA Express host to the SAP HANA Cloud Connector is blocked by a firewall. In that test scenario the SAP HANA Cloud Connector was installed on a Windows machine. The Windows machines firewall blocked the access from the HANA Express host. To solve that issue a firewall exception rule was added which allows the TCP access to the hostname and port of the defined SAP HANA Cloud Connector service channel.

Conclusion

The described scenarios should show some of the options to create remote source connections with free available offers. With that, own tests and analysis can be done without being restricted to a companies infrastructure.