Skip to Content
Technical Articles
Author's profile photo Roman Gorbenko
Roman Gorbenko
December 2, 2020 2 minute read

Integrate it! SAP HANA: Extract data from remote MS SQL Server. Step by step guide

Prerequisites.

You have SAP HANA database running on Linux OS (for example, on RHEL 7.x)
and remote MS SQL Database running on Windows Server OS as a source system. For testing purposes, you want to know more about data extraction from MS SQL Server into SAP HANA through SDA.

Step 1. Installation of the unixODBC Driver Manager (Linux machine)

From root-user:
Step 1.1. Download unixODBC Driver
Download unixODBC Driver  Manager from official site http://www.unixodbc.org/
On Nov.2020 current version – 2.3.9 , download unixODBC-2.3.9.tar.gz
Step 1.2. Upload unixODBC driver to HANA server
Upload for example in /distr folder
Step 1.3. Install unixODBC Driver Manager
cd /distr
gunzip unixODBC*.tar.gz
tar xvf unixODBC*.tar
cd unixODBC-2.3.9
./configure
make
 make install
Links:
Unix ODBC DriverManager official site
unixODBC

Step 2. Install the Microsoft ODBC driver for SQL Server on SAP HANA Server (Linux machine)

Step 2.1. Download driver from the official site
For RHEL 7.X and Microsoft ODBC Driver 17 driver download link will be – https://packages.microsoft.com/rhel/7/prod/msodbcsql17-17.5.2.1-1.x86_64.rpm
Another ODBC Deiver versions (13,11, etc) – https://packages.microsoft.com/rhel/7/prod/
Step 2.2. Upload and install Microsoft ODBC Driver 13 driver
Upload .rpm file for example to /distr directory on SAP HANA server
From root-user:
Install ODBC driver
rpm -i msodbcsql17-17.5.2.1-1.x86_64.rpm
From <sid>adm-user.
Add the path to ENV variable $LD_LIBRARY_PATH:
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/microsoft/msodbcsql/lib64
Add path to ENV variable in .bashrc
nano ~/.bashrc
Add
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/microsoft/msodbcsql/lib64

Step 3. Configure the Microsoft ODBC Driver

Step 3.1. Create MS SQL user
Open MS SQL Management Studio
Connect to Target DB
Security – Users – Create new user
Provide correct permissions for data reading and correct permissions to DB Schema (e.g. dbo or sap or something else)
Step 3.2. Edit odbc.ini file
For example for a database with the name VF3 we can create DSN (Data Source Name) VF3
Typically MS SQL Server uses port 1433 also we need user/password from Step 3.1
nano /etc/odbc.ini
Insert:
[VF3]
Driver=path to the driver from Step 2.1
Description=VF3 Database
Server=ip\instance
Port=1433
Database=VF3
User=<user>
Password=<password>
Step 3.3 Check the connection
From <sid>adm user:
iusql -v VF3 <user> <password>
where VF2 – DSN from Step 3.2
and <user>,<password> – user and password from Step 3.1

Result:

Step 4. Connect SAP HANA to MS SQL Server

Step 4.1.  Open Eclipse / HANA Studio
Provisioning -> Remote Sources – New Remote Source…

Source Name – DSN from Step 3.2
Adapter Name – MSSQL (GENERIC ODBC)
Data Source Name – DSN from Step 3.2
User Name, Password – user and password from Step 3.1
Step 4.2.  Create a Virtual Table
From SAP HANA 2.0 SPS1 this step is optional. We can directly access remote tables and views without having to manually define virtual tables.
Step 4.3.   DML queries on remote data sources 
We can use the following SQL statements:
  • CREATE/ALTER/DROP/REFRESH STATISTICS Statement
  • CREATE SYNONYM Statement
  • DELETE Statement
  • INSERT Statement
  • SELECT Statement
  • REPLACE | UPSERT Statement
  • TRUNCATE Statement
  • UPDATE Statement
Example:
Select Remote Source
In a right-click menu “Open SQL Console”
SQL statement:
SELECT TOP 100 FROM VF3.SAP.LoadXML
Syntax:
<remote_source>.<remote_schema>.<remote_table>

Conclusion.

SAP BASIS Routine task with examples from real life was provided

Links.

Linked Database Overview

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.