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

Prerequisites

In order to enable Datavard Glue functionality, there are multiple prerequisites which 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. In order to enable communication of SAP systems with MS SQL, TPC 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 secondary database in the SAP system and will receive the tables generated by Datavard Glue.

Technical database user

In order to operate the database, a technical user with 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 Window operating environments.

Linux x86_64 operating environment

SAP note 1644499 (https://launchpad.support.sap.com/#/notes/1644499) describes the requirements necessary to establish the connection along with links to the relevant resources.

For the installation of the ODBC driver itself, please follow instructions on 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 (https://launchpad.support.sap.com/#/notes/1774329) describes the necessary steps how to establish the connection along with links to the relevant resources.

Secondary database connection to 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 instance to the system. See https://launchpad.support.sap.com/#/notes/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

Login to SAP system and start transaction /ndbco to define new database connection (switch to change mode and new entries).

The new connection is defined by the attributes below:

DB connection – the 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=skbtsmss01 MSSQL_DBNAME=datavard OBJECT_SOURCE=dbo
Permanent – can be left unchecked
Connection Limit and Optimum Conns - can be left empty if not instructed else

Figure 1 new database connection item

SAP note 1265134 (https://launchpad.support.sap.com/#/notes/1265134) describes in details how to establish the 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.

Enter the connection name and click Execute (F8)
Successful response is shown on the screenshot below

If errors occur, some details can usually be found in ST11 workprocess log.

Storage setup

To create an MSSQL storage:

Open transaction /dvd/sm_setup
Go to Edit Mode & Click New Storage
Enter Storage ID
Select Storage Type SM_TRS_MSS
Enter Description for the storage
Enter previously created DBCO connection as DB Connection Name
Select COLUMN ORIENTED if target database is columnar
Enter MSSQL collation, if non-default value is required
Check Enable update, if delta loads should update previously loaded data

Storage ID: name of the storage
Storage Type: type of the storage. Use the storage type "SM_TRS_MSS" for MS SQL transparent storage.
Description: short text description
DB Connection Name: existing SAP DBCO connection.
Column Oriented: switch between the columnar and row-oriented database management system
MSSQL Collation: collation rules
Enable update: support of direct update statements

Limitations

While altering a table, only new fields (characteristics or key figures) can be added. It isn't possible to modify the existing field’s datatype or length.

Browser not supported