As you are all aware now, you cannot connect DS directly to the SAP BW4HANA system, Hance needs to connect DS DB with the HANA database via SDA and create a source system.
Based on customer requirements set up HANA DB connection with MS SQL DB and set up source system.
Contributor Abhishek Sinha
DISCLAIMER
The content of this blog post is provided “AS IS”. This information could contain technical inaccuracies, typographical errors, and out-of-date information. This document may be updated or changed without notice at any time. Use of the information is therefore at your own risk. In no event shall SAP be liable for special, indirect, incidental, or consequential damages resulting from or related to the use of this document.
Overview:-
Basic Any DB connection architecture with Hana.
Preparation
- Check source and target compatibility by PAM
- Check with the functional team and increase compute based on future growth
- Check odbc LINUX Driver compatibility:- in my case its red hat enterprise Linux 7 (Check OS version from os-release )
Microsoft link for compatibility check:- System Requirements (ODBC Driver for SQL Server) – ODBC Driver for SQL Server | Microsoft Docs
- As my target db is MS SQL DB 2016, as per the document need to install the 13.0 MySQL driver
- Check Installed old installed ODBS drivers
- Required root access for setup
Installation
- Download files from OS
- Update yum update
- take config backup and remove old unixODBC to avoid conflicts
- install MS SQL driver and MySQL tools
- install unixODBC-utf16-devel this step is optional but recommended (Based on MS documentation)
- Create symlinks for tools
Note:- Use the below link and check your OS commands
https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2016
Download files from OS
Download the required OS package by the below command
Update yum update
Update OS yup tool by below command
sudo yum update
Take config backup and remove old unixODBC to avoid conflicts:-
Take a backup of all odbc config files you can find all locations by the below command.
Run the below command to uninstall the old driver version
sudo yum remove unixODBC* (If you use * it will uninstall all old versions)
Install MS SQL driver and MySQL tools:-
Install MS SQL Linux driver and ms tool by the below command
sudo ACCEPT_EULA=Y yum install msodbcsql-13.0.1.0-1 mssql-tools-14.0.2.0-1
after installation check binaries in the below locations
Install unixODBC-utf16-devel this step is optional but recommended:-
Use below command for unixODBC-utf16-devel install
sudo yum install unixODBC-utf16-devel
Create symlinks for tools :-
Create a soft link for MS SQL Linux tools, So that it can be available from everywhere.
Verify link
Check installed driver:-
Configuration
- Update sidadm environment
- Check target DB host and port is accessible for Hana Server
- Create ODBC env file and update target MS db details in odbc driver file
- Create SDA in HANA DB and Test SDA connection
- BW source system creation
- Check consistency and execute
Update sidadm environment :-
Update sidadm LD_LIBRARY_PATH by MySQL driver location, Created .customer.sh file in sidadm home location, and update the below details.
Hana DB default home path is /usr/sap/SID/home
Check target DB host and port is accessible for Hana Server:-
open SQL server configuration Manager and check DB port under properties
Create ODBC environment variable file:-
under Hana sidadm home path create the .odbc.ini file and update target db details.
Check below the MS link for odbc config file syntax.
Connect to SAP HANA via ODBC | SAP Help Portal
.odbc.ini file location :- /usr/sap/SID/home
[Nama for Hana SDA Data source name]
Create SDA in HANA DB and Test SDA connection:-
Login into Hana tenant db by the system user, Go to Provisioning –> Remote source right click and create new Remote source
use same .odbc.ini header name for Data Source Name, provide target MS db user id and password.
Expand Remote Source and check configured targeted MS DB details
Expend DBO user and right click on the table you would like to access and select add as Virtual Table and give schema name.
use virtual table and create view based on your requirement.
BW source system creation: –
Create a logical name for SDA remote DB and go to RSA1 –> SAP Hana Smart data access
Right-click and create a new connection and select the above SDA connection.
Check consistency and execute
Right-click on the new data source and check the connectivity
Reference Documents: –
1868209 – SAP HANA Smart Data Access: Central Note
I hope this document will help you to understand the remote DB connection via SDA.
Cheers!
Anikesh Jyotishi
