Wednesday, 26 August 2015

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

There are several Data Provisioning techniques available within SAP HANA. These are "Smart Data Access" (SDA) and "Smart Data Integration" (SDI). Also available is the Hadoop Integration. This Document will cover the connectivity between SAP HANA 1.0 and MS SQL Server 2012 for SDA and SDI.

In the nature of things it is quite tricky to connect a Linux based Application (In our case SAP HANA 1.0) to a Microsoft Windows based Application (In our case MS SQL Server 2012). The official Documentation guides you to the right direction. But, as so often, it tells only half the truth. This Document will show you the rest of the required Information.

1. Prequel
Please find here some Information before we start. These Information are Resources, Guides, Links, etc.

1.1 Exclusion
This Documents excludes the process of installing and configuring SAP HANA and MS SQL Server 2012. Please consult the official documentation for the correct process.
1.2 Software Versioning
The following Software and its Versions are used:

- SAP HANA 1.0 SPS10 (Rev. 102) on SUSE Linux Enterprise Server 11.3 for SAP
- Microsoft SQL Server 2012 Express Edition on Microsoft Windows Server 2012 R2
- AdventureWorks DW 2012 Sample Database for Microsoft SQL Server 2012
- unixODBC Manager 2.3.0
- Microsoft ODBC Driver 11 for SQL Server - SUSE Linux CTP

1.3 Documentation and Download
The following Documentation helped during the whoe process:

The SAP HANA 1.0 SPS10 Administrators Guide (Page 920)
http://help.sap.com/hana/SAP_HANA_Administration_Guide_en.pdf

The "Install Instructions" section of the Microsoft ODBC Driver 11 for SQL Server - SUSE Linux CTP
Download Microsoft® ODBC Driver 11 for SQL Server® - SUSE Linux Community Technology Preview from Official Microsoft Dow…

The unixODBC Manager 2.3.0
ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gzftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gz

Your Downloads should look like this

SAP HANA Connecting, SAP HANA Tutorial

1.4 Assumption
The following is an Assumption that needs to be considered prior of following these Instructions:

- SAP HANA is installed and working properly
- MS SQL Server is installed and working properly
- Access (root, sidadm/Administrator) to both Hosts is given
- Both Hosts can communicate with each other
- No Firewalls are blocking the connection
- The Microsoft ODBC Driver 11 for SQL Server - SUSE Linux CTP Driver and the unixODBC Manager 2.3.0 is downloaded somewhere
- The System Requirements for the Microsoft ODBC Driver 11 for SQL Server - SUSE Linux CTP have been met. Please check the "System Requirements" section of the Driver for more Information.
  
2. Installation, Configuration and Testing
In this Chapter you will find the Installation and Configuration steps for the Microsoft ODBC Driver 11 for SQL Server - SUSE Linux CTP. You will also find some steps to test the Installation outside SAP HANA.

2.1 Installation of the unixODBC Manager 2.3.0
First we start with the Installation of the unixODBC Manager 2.3.0.

a. Log In as root.

b. Remove any older Version of the unixODBC Manager.

c. Extract msodbcsql-11.0.2260.0.tar.gz.

     tar -xvf msodbcsql-11.0.2260.0.tar.gz

d. Navigate to the msodbcsql-11.0.2260.0 Folder.

e. Start the Installation of the unixODBC Manager:
     ./build_dm.sh --download-url=file:///mnt/sapmnt/software/nonsap/linux/microsoft/unixODBC-2.3.0.tar.gz

f. Type "YES".

SAP HANA MS SQL

g. The Result should look as follows:

SAP HANA Certifications

h. Navigate to the Folder which is highlighted in the lower red rectangle.

     cd /tmp/unixODBC.16887.29613.12297/unixODBC-2.3.0

i. Type "make install".
j. The Result should look as follows:

SAP HANA Database

The installation of the unixODBC Manager 2.3.0 is completed successfully.

2.2 Install the Microsoft ODBC Driver 11 for SQL Server - SUSE Linux CTP Driver
We now continue and instakk the Microsoft ODBC Driver 11 for SQL Server - SUSE Linux CTP Driver.

a. Navigate to the msodbcsql-11.0.2260.0 Folder.

b. Verify if your SAP HANA Host completes all Prerequisites:

  ./install.sh verify

c. The Result should look like this:

SAP HANA Tutorial

d. Run the Installation with:

     ./install.sh install

e. Scroll down and type "YES".

SAP HANA Connecting, SAP HANA SQL

f. The Result should look like this:

SAP HANA Tutorial

The Installation of the Microsoft ODBC 11 for SQL Server - SUSE Linux CTP Driver has been completed successfully. the Driver has been installed to the location "/opt/microsoft/msodbcsql".

SAP HANA Data Provisioning

2.3 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=<YOUR_DESCRIPTION>
  Server=<sqlhost.domain.com\INSTANCE,PORT>
  Port=<PORT>
  Database=<YOUR_DATABASE_NAME>
  User=
  Password=

SAP HANA Certifications

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.

2.4 Test the connectivity
In the next Step we will test the connectivity outside SAP HANA. If it doesnt run on OS Level, it will never run on SAP HANA Level.

a. Log- In as sidadm.

b. Navigate to "/opt/microsoft/msodbcsql/lib64/".

c. Check the Library deoendency of the "libmsodbcsql-11.0.so.2260.0" file with:

     ldd libmsodbcsql-11.0.so.2260.0

d. The Result should look as follows and you should not see any "not found" entry:

SAP HANA Material, SAP Data Provisioning

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

          iusql -v <DSN> <USERNAME> <PASSWORD>

f. The Result should look like this:

HANA Certifications

g. By typing "help" we get a list of the content from the AdventureWorks Database:

SAP HANA Tutorial, SAP HANA Material

h. Type "quit" to leave the Application

The Test run successful.


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

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

SAP HANA Certifications, SAP HANA Data

c. Expand "Provisioning".

d. Right click "Remote Sources" and select "New Remote Source...".

SAP Data Provisioning

e. Enter the required Fields:

SAP HANA Tutorial

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:

SAP HANA Data Provisioning

h. Click on "Test connection"

HANA


i. The Result should look as follows:

SAP HANA Certifications, SAP HANA API

The connection to the Applications has been established successful.

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

SAP HANA Tutorial

b. In our case expant "AdventureWorksDW2012".

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

SAP HANA Connecting

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

SAP HANA MAterial

f. Click "OK".

SAP HANA Certifications

g. Navigate to the Tables of your selected Schema.

SAP Data Provisioning

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:

SAP HANA MS SQL

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

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

4. Appendix
Find here some Appendix Informations that have been gathered over the Time.

4.1 Appendix 1 - Trace the Driver
29.10.2015

If you face Problems during the testing, you can trace the Microsoft ODBC 11 Driver. How you do that can be found here:


4.2 Appendix 2 - SAP HANA Multi Node Deployment
29.10.2015

In a multi Node Setup of SAP HANA you have to install, configure and test the Driver Installation on each Node.

4.3 Appendix 3 - odbc.ini Sample File
29.10.2015

In the Attachment you will find a Sample odbc.ini File. This is a very basic one bot does the trick for first connectivity.

If you have useful hints which other Parameters should be added, please feel free to post them here.

Please be reminded that you have to rename the attached File from "odbc.txt.zip" to "odbc.ini" and place it in "/etc".

Source: scn.sap.com