Skip to Content

SAP Hana EIM Connection Scenario Setup - Part 1

In my documentation I’ll explain how to setup and configure a SAP Hana SP10 EIM (SDI/SDQ) connection with Data Provisioning agent based on Cloud and On-Premise scenario.

This documentation is build in 3 part:

SAP Hana EIM  Connection Scenario Setup - Part 1 (current)

SAP Hana EIM  Connection Scenario Setup - Part 2

SAP Hana EIM  Connection Scenario Setup - Part 3

In my first documentation I have explain how to replicate data over by using Hana SDI capabilities with SAP Hana adapter, in this document I’ll explain how to configure and connect the DP Agent to several source system to retrieve and replicated data for On-Site and Cloud scenario.

I will show in detail step and configuration point to achieve this setup with the following adapter:

Log Reader (Oracle, DB2, MSSQL) / SAP ASE / Teradata and Twitter

Note: The Data Provisioning Agent must be installed on the same operating system as your source database, but not necessarily on the same machine

There are currently three exceptions to this rule:

  1. Oracle on Solaris and the Data Provisioning Agent on Linux is supported
  2. Oracle on HP-UX and the Data Provisioning Agent on Linux is supported
  3. Oracle on Windows and the Data Provisioning Agent on Linux is supported

In order execution

  • Security (Roles and Privileges)
  • Configuration for On-Premise scenario
  • Configuration for Cloud scenario
  • Log Reader adapter setup (Oracle / DB2 / MS SQL)
  • SAP ASE adapter setup
  • Teradata adapter setup
  • Twitter adapter setup
  • Real time table replication

Guide used

SAP Hana EIM Administration Guide SP10

SAP Hana EIM Configuration guide SP10

Note used

2179583 - SAP HANA Enterprise Information Management SPS 10 Central Release Note

2091095 - SAP HANA Enterprise Information Management

Link used

http://help.sap.com/hana_eim

Overview Architecture

On-premise landscape

Cloud landscape

Security (Roles and Privileges)

Before to start trying to make any connection form DP Agent server and Hana, it’s important to provide the necessary credential to the user involve in the configuration based upon your landscape scenario.

For the on-premise and cloud scenario as an administrator ensure you have:

  • Privileges: AGENT ADMIN and ADAPTER ADMIN
  • Application privilege: sap.hana.im.dp.admin::Administrator

For the cloud scenario an additional user is required, its use as a technical user aka “xs agent” required when you need to register an agent:

  • Privileges: AGENT MESSAGING
  • Application privilege : sap.hana.im.dp.proxy::AgentMessaging

Configuration for On-Premise scenario

In the case of on-premise scenario, the interaction with the DP agent and Hana server is done through TCP/IP connection, similar to connection through the Hana Studio.

Configuration for Cloud Landscape scenario

The cloud scenario require in case of SSL connection a specific setup, Hana can be access directly from the internal webdispatcher or access through a proxy, for my configuration I use the direct connection over SSL.

One of the requirement to make Hana available over HTTPS is to have a valid “CommonCryptoLib” library (libsapcrypto.so), by default when Hana is installed it comes with it.

Now from the webdispatcher page in SSL and Trust Configuration tab, I create CA request and send it to my CA Authority and import the response

Once signed I import the CA response in my trusted list of PSE

And change the format view from TEXT to Perm in order to review the chain

Once completed I’ll change the default port used by the webdispatcher in order to use standard port 80/443. In order to do this, from the webdispatcher.ini change the default port to the one you want to use and add the parameter “EXTBIND=1”

Once saved at the os layer you need to bind the default SSL port to use, by default when hana is installed it create an “icmbnd.new” file, rename it to “icmbnd” and change the right on it. You must be root to do this.

Now my Hana instance is available from HTTPS access

The Hana certificate needs to be imported in the DP Agent server, to do this into the “ssl” directory of the DPA.

First change the default “cacerts” password with the following keytool command where the cacerts file is located

keytool -storepasswd  -new [new password ]  -keystore cacerts

Then create a “SAPSSL.cer” file, open it with your favorite editor and paste the entire chain from the imported webdispatcher certificate

And import it into the “cacerts”

Keytool –importcert –keystore cacerts –storepass <password> -file SAPSSL.cer -noprompt

I can now configure DPA to use SSL connection for Hana

LogReader adapter setup

Log Reader adapters provide real-time changed-data capture capability to replicate changed data from Oracle, Microsoft SQL Server, and IBM DB2 databases to SAP HANA in real time; In certain case, you can also write back to a virtual table.

Oracle 12c LogReader adapter

The first point to take in consideration before to start the configuration of any LogReader adapter, is to download the necessary JDBC libraries specific to the source used and store them into the lib directory of the data provisioning agent.

Download the libraries from:

Oracle

Microsoft SQL

IBM DB2

Note for all the database setup, I will not explain how to install them but focus on the step which needs to be perform in order to work with SDI configuration.

In order to enable the real time replication capability in Oracle, a specific script needs to be run on the Oracle database, this script is located in the “scripts” into the DP Agent server

Note: the script assume that the default user for the replication is LR_USER

Before to run it, check if the database is in archivelog mode, if not enabled it needs to be changed

Since the DP agent and Oracle doesn’t reside on the same server, we need to copy the timezome_11.dat file from the Oracle server to the DP Agent server.

And specify the location of the fie from the Oracle preference adapter

Now done I’ll use Oracle SQL Developer to execute the script, which will also create the LR_USER

Note : don’t forget to change the script in order to setup the password for the user.

Now done, I can register my adapter and create my remote connection

From the studio when you specify the OracleLogReader adapter, it’s important to specify the administrator LogReader port and the user define for the replication.

From a connection point with oracle we are done, next MS SQL setup

Microsoft SQL 2008 R2 LogReader adapter

Since EIM relies on database log to perform data movement, which means that logs must be available until the data is successfully read and replicated to Hana, MS SQL Server must be configure in Full Recovery Mode.

For my SQL based scenario it will make my database CDC (change data capture) enable, this feature is supported by EIM but the “truncate” operation on table is not

Once activated, make the check

After the feature is enabled on the database, the cdc schema, cdc user, data capture metadata tables are automatically created

Since I don’t have create table to replicate for now, I’ll explain later how to enable this feature for each table I want to track

Once done enable DAC to allow remote connection from facets

And make the log files readable, copy the sybfilter and sybfiltermgr from the dp server from the logreader folder to the MSSQL server

Anywhere on the server create a file named “LogPath.cfg” and set the variable environment “RACFGFilePath” which point to the location

Open the LogPath.cfg file and provide the location of the .ldf file

Tags:

No comments