Technical Articles
SAP Datasphere integration with SQL server using Data Provisioning Agent
Hello again,
In this blog post we will be discussing setup and configurations required to use Microsoft SQL server as a data source for SAP DataSphere.
Scenario:
- The SQL server is hosted on-premise.
- Remote Tables will need the Data Provisioning Agent.
- The DP Agent will be hosted on-premise and on a Windows server.
Pre requisite:
- The server hosting the Data Provisioning Agent must be able to connect the SQL server over a designated port, This port will eventually be used by Datasphere environment.
- A simple ‘telnet <SQL server> <port>’ test can be performed from DP Agent server to validate the connectivity.
- The DP agent sever must allow connection from Datasphere, firewall rules must be adjusted accordingly.
- Microsoft Visual C++ must be installed on the DP Agent server.
- SAP JVM is required and is bundled with the Data Provisioning Agent installation package.
- The Datasphere environment must whitelist the DP agent server IP.
- The DataSphere out bound and HANA IPs must be allowed on the DP Agent server.
SAP HANA smart data integration:
Data Provisioning Agent is delivered as part of SAP HANA smart data integration (SDI).
Latest version – SAP HANA SDI 2.0 information is available in Product Availability Matrix (PAM).
All essential compatibility, system requirement, release and maintenance information is available in the document provided in PAM.
DP Agent is available for hosting on Linux and Windows Operating systems.
Minimum Hardware Requirements
* 4 cores with minimum 2 GHz and 16 GB RAM are recommended
* Disk Space Requirements (not including Operating System) – 30 GB for default installation.
Set up the Data Provisioning Agent:
- Download DP Agent media – Follow the media download path SAP HANA SDI > SAP HANA SDI 2.0 > COMPRISED SOFTWARE COMPONENT VERSIONS > HANA DP AGENT 2.0
Select the Operating System and download the relevant version
2. Extract the software media in a local drive
3. Run as administrator
The installer ‘hdbsetup’ will be used for installation of new Agent
Keep in mind that you need to provide a location for installation in the next step, so the location must already be created.
points to note –
-Agent name must be unique.
-The user must be a domain user and must already exist.
-The agent listener and administrator ports – 5050 and 5051 are default ports but different ports can be used. Make sure that the ports are adjusted on the firewall.
Continue the setup procedure and wait for successful installation message.
Check ‘services.msc’ to validate if the service for the agent has been created. the services must be created with the domain user that was provided during the installation.
—-
Error If for some reason the service is not created and the installation logs also mention.
then Run cmd as administrator, from the <Agent installation directory>\bin directory execute the following:
dpagent_servicedaemon_install.bat <domain user> <random string for password>
this command will not return anything but will create the service.
—-
Installation logs are available here – <installation directory>\log> and in the temp directory.
Agent Administration:
<Drive>:\usr\sap\<agent install location>\bin>agentcli.bat -configAgent
Agent can be stopped and started using option 2.
—-
Error the agent doesn’t stop with the configuration tool. then stop respective service manually and kill the relevant PID from task manager.
—-
Newly installed ‘agent status’ should show something like below. this is the default configuration.
Agent Configuration – Connection with SAP HANA/Datasphere:
-> Log in to the DataSphere tenant and Navigate to
System -> Configuration -> Data Integration -> on-premise Agents
click to add new agent
clicking create will provide further details
the details from this screen will be used in DP Agent configuration.
-> Log in back to the DP agent server – in the <Agent installation folder>, open the ‘dpagentconfig.ini‘ file. Make a copy of this file before changing as a good practice.
update following values:
agent.name = <agent name as in DS>
hana.port = <from the DS screen>
hana.server = <from the DS screen, this is where DS HANA server is running>
jdbc.enabled = true
jdbc.host = <same as HANA host>
jdbc.port = <same as HANA port>
save the configuration file and restart the agent.
Now when you see the status of the agent it must show DPAgent name, HANA server, port and protocol as configured.
-> Configure HANA user and password
run ‘cmd’, from the ‘<agent installation location>\bin‘ – agentcli.bat –setSecureProperty
select option 1 to set HANA user and then option 2 to set HANA password, remember both the values were provided to us in the earlier screen from DataSphere.
Restart the agent again.
Wait for some time.. couple of minutes to check that the agent is now connected to DataSphere.
<if this doesn’t turn green soon it may require troubleshooting, check the agent logs>
Adapter configuration:
In order to connect DataSphere/Data Provisioning Agent to the Microsoft SQL server there is a need to have the JDBC driver.
Check the JDBC driver compatibility with the SQL server from the PAM guide (details above).
for eg. Let us assume that we need to connect to SQL server 2016
the adapter that we need is logReader Adapter
and so we can use JDBC 8.4 or 9.2
Download the JDBC driver and place it here ‘<Agent installation location\lib>’
In DataSphere, Navigate to
System -> Configuration -> Data Integration -> on-premise Agents
Open the agent that we configured and click edit, select the sqlLogReader adapter and save
Create the connection:
The last step in this configuration is to create a connection in SAP DataSphere.
In the navigation panel select ‘connection‘ and select the DS space of choice
click on create
Select connection type
For Remote tables the new configured DP agent must be selected
There is an option to select windows authentication, remember that we must provide windows credentials in the earlier step if we are using this option.
Conclusion:
The steps discussed above will enable the Remote tables only.
Data Flows on the other hand will require SAP Cloud Connector.
Troubleshooting and other help:
SAP PAM and guide for SDI – https://userapps.support.sap.com/sap/support/pam?hash=s%3DSAP%2520HANA%2520SDI%25202.0%26o%3Dmost_viewed%257Cdesc%26st%3Dl%26rpp%3D20%26page%3D1%26pvnr%3D73555000100900001404%26pt%3Dg%257Cd
Prepare SQL server connectivity –https://help.sap.com/docs/SAP_DATASPHERE/9f804b8efa8043539289f42f372c4862/ea69328fb328449ab7b04d2b142592f8.html
SAP note – 2557808 – Can you configure the SQL Server Log Reader Adapter using Windows authentication? – SAP HANA Smart Data Integration
SAP note – 3219002 – Failed to create Windows service for DP Agent as part of the installation
Remote source configuration – https://help.sap.com/docs/HANA_SMART_DATA_INTEGRATION/7952ef28a6914997abc01745fef1b607/444b1a87758345f6b39ba481a388cc7d.html?version=2.0_SPS03
Configuring Windows authentication – https://help.sap.com/docs/HANA_SMART_DATA_INTEGRATION/7952ef28a6914997abc01745fef1b607/a8886ad541504221a89ce3a976c45117.html?version=2.0_SPS03
Regards,
Shoeb