Skip to Content

Client side libraries used by SAP/ABAP for SQL Server

1. Interface to the Database

In order to store and retrieve data from a database, a program needs to use an interface that is dependent on that particular DBMS (Database Management System).

As of R/3 kernel Release 4.5A, the database dependent part of the R/3 database interface is stored in a separate library, the DBSL, and so the ABAP implementation consists of:

  • The DBSL (Database Shared Library), a database-dependent part of the SAP kernel that is dynamically linked to the SAP kernel.
  • The database client tools, i.e. some libraries that are usually provided by the database manufacturer. These are either statically or dynamically linked to the database library.

Note that a Java stack also needs an interface, but it uses a completely different technology (the JDBC). This is beyond the scope of this article.

1.1. SAP’s DBSL

The ABAP stack of an SAP system installed on a SQL Server database needs such an interface to access the <SAPSID> database; in the same way it is necessary in order to access an external SQL Server through a DBCON (Multiconnect) connection, e.g. to use the external database as a data source in an SAP BW system, or to centrally monitor several databases of your corporate landscape from an SAP Solution Manager (transaction DBACOCKPIT).

The dynamic link library that SAP delivers so that an ABAP stack is able to connect to a MS SQL Server database is called dbmssslib.dll (check note 400818 for further details on this naming convention). It is distributed with the kernel, but you can also download it separately from the SAP Service Marketplace (LIB_DBSL.SAR, on the database dependent part of the kernel).

It must be installed in the ABAP kernel executable directory (DIR_EXECUTABLE) of the SAP application server that is to access the <SAPSID> database (or any other SQL Server external database). Note that the fact that it is a DLL implies that it can only be installed on a Microsoft Windows platform; as the Microsoft Data Access technologies are not available on other platforms, SAP did not implement any other “dbmssslib” for non-Windows platforms; this implies that you cannot access from an SAP application server running e.g. on a Unix host to a SQL Server database directly.

1.2. Loading the Database interface at startup

When initiating an SAP system, the database-dependent database library is loaded before the DBSL is called for the first time. The system searches for the library in the directory indicated by the environment variable DIR_LIBRARY (e.g. /usr/sap/<SAPSID>/SYS/exe/run). The environment variable dbms_type contains the name of the required database management system. When the system is initiated, an attempt is made to load the library belonging to the required database management system from the directory indicated by the environment variable DIR_LIBRARY.

2. Short notice on Microsoft Data Access technologies

Among a large list of possibilities, Microsoft delivers ODBC and OLEDB for general access to data sources, as well as the SQL Server Native Client that can only be used for MS SQL Server databases and, so, it is highly optimized.

  • ODBC (Open DataBase Connectivity): It is a call-level access (i.e. API functions) for C/C++ applications to varying data stores through ODBC drivers. ODBCconf.exe is a command-line utility for configuring drivers and data source names (DSNs).
  • OLEDB (Object Linking and Embedding, DataBase): It is an object-level access (i.e. set of COM-based interfaces) that expose data from a variety of sources through OLEDB providers to be accessed by C/C++ applications.

These are the ones that are used by an ABAP stack. For more information on the available technologies, you can check http://msdn.microsoft.com/library/ee730344.aspx.

2.1. MDAC (Microsoft Data Access Components)[i]

In order to connect their applications to a relational database, developers can use a variety of providers and drivers that are shipped by Microsoft, or by third parties. MDAC is one of these interfaces and it is part of the operating system. It implements OLEDB (CLSID_MSDASQL) and ODBC drivers for SQL Server. It requires a separate connection for each active select. The active select referred to here is the select using a client, or ‘firehose’ cursor.

We learned quite early that the ‘normal’ select via server side cursor is quite expensive. So whenever possible we use the client side cursor method, which means that we just issue the select and process the rowset. The drawback of this really fast method used before MARS (check next paragraph), was that it blocked the socket (a socket defines the database connection through the network as a file descriptor defines the access to a local file) for all other operations until the rowset was read. So we mainly exploited this method for uncommitted reads, using before MARS multiple additional database connections. So an SAP connection consisted of N uncommitted read connections and one committed read connection (which handled the committed reads, the blocking reads, the modifications). In case of committed read we used the firehose/client side cursor method only for single selects or certain special cases.

Nowadays, MARS (Multiple Active Result Sets) allows the handling of multiple rowsets in one database connection. So, now with MARS, the SAP connection consists of only two database connections: one for committed read (where we still use server side cursors) and one for uncommitted reads (where we handle parallelism by using MARS).

2.2. SNAC (SQL Server Native Client)

SQL Server Native Client is a stand-alone data access application programming interface (API) that includes OLEDB (CLSID_SQLNCLI) and ODBC drivers. It was first shipped with SQL Server 2005 (SNAC 9.0).

SNAC supports MARS (Multiple Active Row Sets) which allows a single connection to simultaneously support multiple active selects.

The SNAC software is distributed by Microsoft with the SQL Server 2005 and later versions as the file sqlncli.msi. You should look fora version suitable for your hardware platform and install it on your application server.

It is important that you install the SNAC 2005 SP1 or later (check note 960985). You should also make sure that you install in all the SAP application servers the SNAC version that matches your SQL Server version (or a later one, according to note 1082356). If this is not done, unexpected issues can take place.

Older releases of the ABAP/DBSL interface use OLEDB. DBSL 7.00/7.01 implements both the older OLEDB and the newer ODBC version.  DBSL 7.10 and later implements only the ODBC version. Exception: special 7.10 and later DBSL DLLs are available for use with SQL 2000 where supported (dbmssslib_oledb.dll).  This is mainly because SQL Server 2000 does not support MARS, and the ODBC DBSL requires MARS.

The ODBC DBSL will always try to use the latest available SNAC ODBC driver.  The SNAC ODBC driver is implemented by SQLNCLI*.DLL.  Microsoft guarantees that newer versions are backward compatible with previous server versions.

3. Compatibility and limitations

The internal implementation of the DBSL needs to interface with the database-specific technology that was released by Microsoft to communicate to the various data stores. SAP developers used the MDAC initially, but also implemented SNAC as soon as it was released by Microsoft to take advantage of its benefits.

Although it is possible to use MDAC 2.8 to access a SQL Server 2005 database, Microsoft released the SNAC as a more powerful interface. Both provide native data access to SQL Server databases, but the SQL Server Native Client has been specifically designed to expose the new features of SQL Server 2005 (such as MARS, user-defined data types, query notifications, snapshot isolation, and XML data type support), while at the same time maintaining backward compatibility with earlier versions.

Nowadays both MDAC and SNAC are possible depending on the SAP release and SQL Server version. Generally speaking, SQL Server 2000 did not allow MARS and was accessed through OLEDB, but the later SAP releases require ODBC. It is not intended to describe in detail the differences in this article, but in case you are interested on this topic we recommend the following articles as a good starting point:

http://msdn.microsoft.com/en-us/library/ms810810.aspx

http://msdn.microsoft.com/en-us/library/ms131035.aspx

http://msdn.microsoft.com/library/ee730344.aspx

The point is that now we are facing a wide range of possible combinations, some due to technical improvements and limitations and some others developed for compatibility. In order to be able to know which combinations are supported we present the following table:

Tabla.jpg

4. How to check which client library my SAP system is indeed using?

To check the SQL Server and driver version use SM50, select a work process and view the developer trace file.  Look for the following (ODBC):

     C  Thank You for using the SLODBC-interface
     …
     C  ODBC Driver chosen: SQL Server Native Client 10.0 native
     C  lpc:(local) connection used on <server>
     C  Driver: sqlncli10.dll Driver release: 10.50.1804
     C  GetDbRelease: 10.50.1702.00
     C  GetDbRelease: Got DB release numbers (10,50,1702,0)

Or (OLEDB):

     C  Thank You for using the SLOLEDB-interface
     …
     C  Provider Release:9.00.4035.00
     C  Using Provider SQLNCLI
     …
     C  Using MARS (on sql 9.0)

5. References

SAP note 400818 – Information about the R/3 Database Library

SAP Note 323151 – Several DB connections with Native SQL

SAP Note 178949 – MSSQL: Database MultiConnect with EXEC SQL

SAP Note 734034 – Native OLEDB provider SQLNCLI

SAP Note 738371 – Creating DBCON multiconnect entries for SQL Server

SAP Note 960985 – existing Stored Procedure erroneously considered as missing

SAP Note 1082356 – Using the ODBC based DBSL for Microsoft SQL Server

SAP Note 1238905 – Connection is busy with results for another command

SAP Note 1248222 – ODBC DBSL profile parameters and connect options

SAP Note 1263367 – Accept MDAC driver for DBCON

SAP Note 1341097 – MSSQL: 720 DCK, 7.0* on SQL 2000, dbmssslib_oledb.dll

SAP Note 1506487 – Error 3997 when executing native SQL

SAP Note 1644499 – How to set up a connection to MS SQL Server from Linux

SAP KBA 1544360 – SQL Error 402 during DB compression with report MSSCOMPRESS

http://msdn.microsoft.com/library/ee730344.aspx

http://msdn.microsoft.com/en-us/library/ms810810.aspx

http://msdn.microsoft.com/en-us/library/ms131035.aspx

http://help.sap.com/saphelp_nw04/helpdata/en/f3/914f3445194d468f652d45494230b1/content.htm


[i] Starting with Windows Vista, the data access components are now called Windows Data Access Components, or Windows DAC

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply