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:
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:
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)
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)
[i] Starting with Windows Vista, the data access components are now called Windows Data Access Components, or Windows DAC