- SAP Managed Tags:
Smart Data Access (SDA) is slightly older feature in HANA .I recently got a chance to setup for SQL Server12 . I read lot of documents/blogs/you tube videos but still running into issues and couldn’t implement in first go. It took me two or three iterations to get it installed correctly. I decided to put exact steps one should follow to implement SDA for SQL Server. I believe with these steps one should be able to install SDA for SQL Server in first go without wasting much time.
SDA Definition:
SAP HANA smart data access enables remote data to be accessed. It enables remote data to be accessed as if they are local tables in SAP HANA, without copying the data into SAP HANA. Specifically, in SAP HANA, you can create virtual tables which point to remote tables in from data sources. It is possible to write queries in HANA combining Hana native tables and virtual tables.
SDA Architecture:
We are configuring HANA Server SH1 and connecting to SQLSERVER and another HANA Database.
Steps to Configure SDA :
a. Linux Users
b. Download / INSTALL unixODBC Driver Manager
c. Download / INSTALL SQL Server Drivers
d. SETUP .odbc.ini file
e. Testing
f. Setup SDA for SQL server in HANA STUDIO
a. Linux Users
I am sure everyone knows about users but just wanted to reiterate .When you install Hana with root user it automatically creates a user <SID>User. If you don’t know the <SID>User you can run following command and know the <SID>User.
>> cat /etc/passwd
Root User:
With root user you will setup complete configuration of HANA System that including UnixODBC and other ODBC drivers.
<SID>user:
<SID>User may not have permissions to setup configurations files unless he is given special permissions. Generally all HANA configurations are done with root user.
Hana Studio is owned by <SID>User so some of the configurations you did as root user the others users in the system can’t see those configuration files .
b. Download / INSTALL unixODBC.2.3.0 drivers Manager.
Download the update the unixODBC driver to 2.3.0
Once you download this driver (unixODBC-2.3.0.tar.gz) move it to HANA Server (can use FileZilla or other file transfer s/w) .Go to the directory when you have copied this file.
Login as root user and go to the directory when you copied the drivers.
>> gunzip unixODBC-2.3.0.tar.gz
>> tar xvf unixODBC-2.3.0.tar
>> ls
Will show you the unixODBC-2.3.0 folder , type the following command to install Unixodbc drivers .
>> cd unixODBC-2.3.0
>> ./configure
>> make
>> make install
Once the making of binaries are done check if the drivers installed properly.
>> isql –version
The output should be unixODBC 2.3.0
c . Download /Installing SQL Server Drivers :
Download MS SQL SERVER Driver for Suse Linux:
(google for other flavors of Linux drivers for SQLSERVER)
http://www.microsoft.com/en-us/download/details.aspx?id=34687
Once you download , copy the SQL server drivers to HANA Server ( May be at same location where you downloaded /installed UnixODBC file using filezilla or other file transfer software)
INSTALL MS SQL server drivers: unzip the file:
Login as root user
>> gunzip msodbcsql-11.0.2260.0.tar.gz
>> tar –xvf msodbcsql-11.0.2260.0.tar
This will install a folder with msodbcsql-11.0.2260.0 and also it will install in /mnt file directory
to check whether it installed SQL server drivers properly check the following path sql odbc directory.
>> cd /mnt/drivers/msodbcsql-11.0.2260.0
Verify the version and install:
>> ./install.sh verify
>> ./install.sh install
Check again to see if it’s installed.
>> ./install.sh verify
d. SETUP .odbc.ini file
Check the odbc.ini file by using the command
Login as root user
>> odbcinst –j
Odbc.ini file is visible at two locations .One at root directory and one at /etc/unixODBC/odbc.ini
Note: if you want all the users to have access to .odbc.ini file then add config in root user and add entries in “/root/.odbc.ini” file
>> vi /root/.obdc.ini -- Open the file and enter following and save it
---------------------------- MS SQL SERVER DRIVERS --------------------------
[MSSQL]
Server=SQLSVR,1433
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0
----------------------------------------------------------------------------------------------
e. TESTING
Login to Hana Server with Root User (in Putty) and test connectivity for Sql Server :
Login As root user
>> isql -v MSSQL sa Welcome1
This works for root user .
f. Add data source from HANA studio
- Error : SAP DBtech JDBC L403) Internal error Cannot remote source objects [unixODBC] Data Source name not found and no default driver specified.
Is you see the above error which tells there is a problem with unixODBC drivers but the error message is misguiding .This means the HANA Studio cannot see some of the configuration files in HANA HOME Directory.Remember .Hana Studio is owned by <SID>USER..
Do the following
Log in HANA Server as root user in putty
>> cp /root/.odbc.ini /usr/sap/<SID>/home (ex: cp /root/.odbc.ini /usr/sap/SHI/home)
Logout of Hana Server and come to HANA Studio and try to connect SQL Server .I got success when I do this process.
Configuring Other Hana Server (Server sid name : FDC )
Login to HANA Server (SH1 ) as root user
>> vi /root/.odbc.ini
---------------------------- MS SQL SERVER DRIVERS --------------------------
[MSSQL]
Server=SQLSVR,1433
Driver=/opt/icrosoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0
--------------------------------- HANA SYSTEM (FDC) ---------------------------------------
Server=FDC: 30015
Driver=/usr/sap/FDC/hdbclient/libodbcHDB.so
>> cp /root/.odbc.ini /usr/sap/SHI/home ( For HANA Studio to see this file )
Connecting Other Databases: Will update this section when I configure .
Conclusion:
Now we have connected to SQL Server and different HANA Server. This worked for me and hope it works for you as well in your first go. Whenever I add other databases as sources I will update the process with my findings in this blog and thanks for reading my blog. :smile:
3 Comments
