(SM-1905) MS SQL Transparent Storage

Owned by SNP
Apr 30, 2019
3 min read

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 configuration done in previous steps.

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 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 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

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 report ADBC_TEST_CONNECTION to test if the connection is correctly configured.

  1. Enter connection name and click Execute (F8)
  2. 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:

  1. Open transaction /dvd/sm_setup
  2. Go to Edit Mode & Click New Storage
  3. Enter Storage ID
  4. Select Storage Type SM_TRS_MSS
  5. Enter Description for the storage
  6. Ented previously created DBCO connection as DB Connection Name
  7. Select COLUMN ORIENTED if target database is columnar
  8. Enter MSSQL collation, if non-default value is required
  9. 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.