(SM-2411) MS SQL Storage Setup

Owned by SNP
Nov 20, 2024

This document serves as a guide for the implementation of the necessary prerequisites to operate SNP Glue™, a middleware and development platform for data integration, in combination with Microsoft SQL environments.

Table of Contents:

Prerequisites

To enable the SNP Glue™ function, multiple prerequisites need to be met. They are divided into three categories:

  • MS SQL
  • Operating system
  • SAP

Some of these prerequisites can be achieved or set up in parallel, others have to be done sequentially as they depend on the configuration done in previous steps.

Supported versions

The minimal supported version is 12.00.5207.

MS SQL

Open ports

In a controlled network environment, it is common to have firewall rules in place. To enable communication of SAP systems with MS SQL, the TCP port 1433 needs to be reachable in MS SQL from the SAP system.

Database creation

On the MS SQL server, a dedicated database needs to be created. It will then be added as a secondary database in the SAP system and receive the tables generated by SNP Glue™.

Technical database user

To operate the database, a technical user with full access to the database needs to be configured.

Operating system

Two software components are necessary to connect your SAP Application Server to a remote SQL Server instance:

  • The Microsoft ODBC driver
  • The SAP DBSL library

The connection can be established from both Linux and Windows operating environments.         

Linux x86_64 operating environment

SAP Note 1644499 describes the requirements necessary to establish the connection and links to the relevant resources.

For the installation of the ODBC driver itself, please follow the instructions on the Microsoft website:

https://docs.microsoft.com/en-gb/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

Windows operating environment

SAP Note 1774329 describes the necessary steps on how to establish the connection along with links to the relevant resources.


Secondary database connection to the MS SQL server is currently supported by SAP only from Windows and Linux application servers.
For systems based on AIX, HP-UX, or Solaris, the only workaround is to add Linux or Windows instances to the system. See the SAP Note 2221305.


SAP: Connection of a remote database

Once the database connectivity from the SAP application server to the remote SQL server has been established, it is possible to add the MS SQL database created beforehand as a secondary database to the SAP system.

Establishing the connection

Log in to the SAP system and start the transaction /ndbco to define a new database connection (switch to change mode and new entries).

The new connection is defined by the attributes below:

  • DB connection: Name of the database connection (e.g. MSSQL)
  • DBMS: MSS can be chosen via F4 help
  • Username: MS SQL technical username
  • DB password: MS SQL technical user password
  • Info: Enter connection string:
    • MSSQL_SERVER=<mssql_server> MSSQL_DBNAME=<database> MSSQL_SCHEMA=<schema>
  • Permanent: Can be left unchecked
  • Connection Limit and Optimum Conns: Can be left empty if not instructed otherwise

New database connection item

SAP Note 1265134 and SAP Note 178949 describe in detail how to establish a connection to a remote database.

Testing the connection

In transaction SE38 you can run the report ADBC_TEST_CONNECTION to test if the connection is correctly configured.

  1. Enter the connection name and click Execute (F8)
  2. A successful response is shown in the screenshot below

If errors occur, some details can usually be found in the ST11 work process log.

Storage setup

To create an MSSQL storage:

  1. Open transaction /DVD/SM_SETUP
  2. Click Create
  3. Enter Storage ID
  4. Select Storage Type SM_TRS_MSS
  5. Enter the Description for the storage
  6. Enter previously created DBCO connection as DB Connection Name
  7. Select COLUMN ORIENTED if the target database is columnar
  8. Enter MSSQL collation, if a non-default value is required.
    1. To differentiate between uppercase and lowercase letters, it is necessary to fill the MSSQL collation field with Latin1_General_CS_AS.
    2. SAP Note 2767546
    3. https://www.sqlines.com/oracle/case_sensitivity_primary_key
  9. Check Enable update, if delta loads should update previously loaded data
  10. Check Map boolean to bit type, if you want to map SAP fields with domains FLAG and XFELD to MSSQL data type bit

  • Storage ID: Name of the storage.
  • Storage Type: Type of storage. Use the storage type SM_TRS_MSS for MS SQL transparent storage.
  • Description: Short text description.
  • Connection mode: O for Open SQL, N for Native SQL.
  • DB Connection Name: Existing SAP DBCO connection.
  • Column Oriented: Switches between the columnar and row-oriented database management system.
  • MSSQL Collation: Collation rules.
  • Enable update: Support of the direct update statements.
  • Map boolean to bit type: Maps the SAP fields with domains FLAG and XFELD to MSSQL data type bit.
  • Read all clients: Reads the data from all clients.

Limitations

While altering a table, only new fields (characteristics or key figures) can be added. Modifying the existing field’s data type or length is impossible.