This document serves as a guide for implementing 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

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

Technical database user

A technical user with full access to the database must be configured to operate the database.

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.

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. Enter the database schema if required. If specifying the schema in the connection string does not work, you can use the DB Schema parameter. However, note that specifying the schema here will result in queries being automatically executed as native SQL.
8. Select COLUMN ORIENTED if the target database is columnar
9. 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
10. Check Enable update, if delta loads should update previously loaded data
11. 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.
• DB schema: An optional parameter that specifies the database schema to be used. When this parameter is provided, native SQL is automatically used to execute queries.
• 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.