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

Your Downloads should look like this

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

g. The Result should look as follows:

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:

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:

  d. Run the Installation with:

     ./install.sh install

e. Scroll down and type “YES“.

f. The Result should look like this:

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

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=

 

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:

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

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

f. The Result should look like this:

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

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.

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.

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.

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

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:

Data Access Tracing with the ODBC Driver on Linux

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