SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability26
After the SAP process, SAPService<SID> establishes a connection using integrated security. The process acts as a user owning a schema in the SAP database by executing SETUSER <SID>.
For troubleshooting, note the following:
When accessing a SAP database with SQL Server 2005 query tools, the SETUSER <SID> statement must be executed before data can be read.
Because the SAP customer can install more than one schema in one SAP database, ensure that the schema is correct. Most schemas have a set of objects that are named in the same manner in both schemas.
Note that this is not a security gap because SQL Server 2005 installs accepted Integrated Security logins only.
The preceding SAP security considerations involve the last two releases of the SAP NetWeaver Application Server, which are supported by SQL Server 2005. In older releases, a SAP database could contain only one schema owned by the dbo database role.
SAP statement execution
NetWeaver Application Server executes a combination of parameterized statements and static stored procedures against SQL Server 2005. The intention is to cache and reuse the query plans resulting from those parameterized statements and parameterized stored procedures. This keeps the number of statement compilations and recompilations extremely low (usually in the low single digits per second).
SAP can dynamically create and reuse static stored procedures. A stored procedure usually contains one statement only. SAP products can contain up to several hundred thousand stored procedures. SAP identifies static stored procedures by using naming conventions derived from the ABAP source code. When the ABAP source code changes, new stored procedures can be created on the fly.
SAP uses a minimum number of ad hoc queries—less than one per second.
There are relatively few database locks, because 99.9 percent of all reads are uncommitted. The number of database locks typically ranges from a few thousand to ten thousand or more.
With SQL Server 2005, all SAP statement and stored procedure executions are prepared by using the SAP application, making it extremely difficult to use SQL Server Profiler to trace those executions.
With SQL Server 2005, SAP only supports the OLE DB programming interface integrated in SQL Native Client. SQL Native Client needs to be installed on each application server to allow SAP NetWeaver Application Server to connect to SQL Server 2005. SQL Native Client contains the Client APIs of SQL Server 2005. The Client APIs are deployed by SQL Server only and are independent of Microsoft Data Access Components (MDAC).
Microsoft Corporation © 2005