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
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
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:
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