SAP HANA to MSSQL SDA 설정

1. os 버전 확인

cat /etc/*release*

2. Microsoft ODBC Driver 13.1 for SQL Server 설치

https://docs.microsoft.com/ko-kr/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15

Linux 및 macOS 기반 Microsoft ODBC Driver for SQL Server 설치 - SQL Server

 

3. lib 복사 및 odbc.ini 파일 설정

https://blogs.sap.com/2015/10/29/connecting-sap-hana-10-to-ms-sql-server-2012-for-data-provisioning/

Connecting SAP HANA 1.0 to MS SQL Server 2012 for Data Provisioning | SAP Blogs

Configure the Microsoft ODBC Driver 11 for SQL Server – SUSE Linux CTP

Now we can go ahead and configure everything after the Installation.

a. Navigate to “/opt/microsoft/msodbcsql/lib64“.

b. Copy the File “libmsodbcsql-11.0.so.2260.0” to your SAP HANA Directory.

cp libmsodbcsql-11.0.so.2260.0 /usr/sap/HA1/HDB01/exe

c. Navigate to “/etc“.

d. Open the File “odbc.ini” via:

vi odbc.ini

e. Paste the following content after you adjusted it your your Environment:

[advwrk12]

Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0

Description=

Server=

Port=

Database=

User=

Password=

f. Save and Close the File.

g. Log In as sidadm.

h. Navigate to your Home Directory and open your Profile File.

vi .profile

i. Create the ODBCINI Environment Variable:

ODBCINI=”/etc/odbc.ini”

export ODBCINI

j. Save and Close the File.

k. Log Off as sidadm and Log In back again.

l. In order that your SAP HANA Instance takes notice of this Environment Variable you need to restart your Instance.

The configuration ended successfull.

 

4. 연결 테스트

e. Test the connectivity with the “iusql” command from the unixODBC Manager:

iusql -v DSN USERNAME PASSWORD

 

5. Provisioning 및 Virtual Table 생성

Connect SAP HANA to MS SQL Server 2012

Finally we are able to connect our SAP HANA Instance to the MS SQL Server 2012 and import a Table.

 

Connect SAP HANA to MS SQL Server 2012

First we connect the two Applications.

a. Launch the SAP HANA Studio.

b. Log In to your Database.

c. Expand “Provisioning“.

d. Right click “Remote Sources” and select “New Remote Source…“.

e. Enter the required Fields:

Please note that “Data Source Name” must match with your DSN entry in the “odbc.ini” File. the DSN is in between the “[ ]“.

f. Save your Changes.

g. You should see the following Result:

h. Click on “Test connection“

i. The Result should look as follows:

The connection to the Applications has been established successful.

 

Import a Table

At the End we will import a MS SQL Server based Table to a SAP HANA Schema.

a. Expand your Remote Connection.

b. In our case expant “AdventureWorksDW2012“.

c. Expand the “dbo” Schema. You will see all available Tables:

d. Right cklick the Table you wish and select “Add as Virtual Table“.

e. Give it a Name, select your target Schema within SAP HANA and click “Create“.

f. Click “OK“.

g. Navigate to the Tables of your selected Schema.

h. Right Click your imported Table and select “Open Data Preview“.

i. You will see the MS SQL Server 2012 Data inside the SAP HANA Studio:

The import process completed successful.

Now you can continue to import more Tables and proceed with your Development.

NOTE: In the SQL Server Management Studio Activity Monitor you can now see our open Connection