SAP BW4HANA DS connation (MS SQL DB) Source system via SDA

Introduction:

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.

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 )

◉ 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

1. Download files from OS

2. Update yum update

3. take config backup and remove old unixODBC to avoid conflicts

4. install MS SQL driver and MySQL tools

5. install unixODBC-utf16-devel this step is optional but recommended (Based on MS documentation)

6. 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

.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

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