This chapter explains how to meet the necessary prerequisites to operate Kyano Glue, a middleware and development platform for data integration, in combination with Microsoft SQL environments. 

Table of Contents:

Prerequisites

The prerequisites required to enable Kyano Glue functions are divided into three categories: 

• MS SQL

• Operating system

• SAP

You can implement some of these prerequisites in parallel. However, you must implement others sequentially because they depend on the configuration that was saved in previous steps. 

Supported versions

The minimum 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 SAP systems to communicate with MS SQL, the TPC port 1433 must be reachable in MS SQL from the SAP system. 

Database creation

You must create a dedicated database on Microsoft SQL Server. This database will then be added as a secondary database in the SAP system and will receive the tables generated by SNP Glue. For more information, see the subchapter Connecting a Remote Database in SAP below. 

Technical database user

To operate the database, you must create a technical user with full access to the database. 

Operating system

Two software components are necessary to connect your SAP application server to a remote Microsoft SQL Server instance: 

• The Microsoft ODBC driver

• The SAP Database Shared Library (DBSL) 

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

Linux x86_64 Operating Environment

SAP Note 1644499 describes the necessary steps for establishing the connection, along with links to the relevant resources.

To install the ODBC driver itself, see the official Microsoft documentation. 

Windows operating environment

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

Connecting a Remote Database in SAP 

Once you have established the database connection from the SAP application server to the remote MS SQL server, you can add the MS SQL database created beforehand as a secondary database to the SAP system. 

Proceed as follows to define a new database connection: 

1. Start transaction /NDBCO. 

2. Activate Edit mode. 

3. Click New Entries. 

4. Fill out the fields as follows: 

   • DB connection: Name of the database connection, e.g., MSSQL.

   • DBMS: Name of the relational database management system. Use the F4 help to select MSS (Microsoft SQL Server).

   • Username: Technical username for MS SQL.

   • DB password: Technical user password for MS SQL.

   • Conn. Info: Connection information. Example of a connection string: MSSQL_SERVER=skbtsmss01 MSSQL_DBNAME=datavard OBJECT_SOURCE=dbo. 

   • Permanent: This option determines how the system reacts when a database connection is lost due to a failure or network issue. We recommend selecting this option if the connection is critical or frequently used. Possible values: 

     • <empty>: The system ignores the failure and tries to continue. However, if the missing connection is needed, the transaction fails. 

     • X: The system checks the connection before every transaction. If the connection cannot be restored, the transaction is not started. The system will only work when all essential connections are restored. 

   • Connection Limit and Optimum Conns: Leave these fields unchanged unless instructed otherwise.

5. Click Save. 

The new database connection has been defined. 

Open

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

Testing the connection

Testing the Connection 

Proceed as follows to test if the connection is correctly configured: 

1. Start transaction SE38. 

2. Execute the report ADBC_TEST_CONNECTION. 

3. Enter the DB connection name. 

4. Click Execute. 

A successful response shows the MSSQL connection with the status Successfully opened. 

If errors occur, check the work process log in transaction ST11 for more details. 

Creating MSSQL Storages in Kyano Storage Management 

Proceed as follows to create an MSSQL storage: 

1. Start transaction /DVD/SM_SETUP. 

2. Click Create to create a new storage. 

3. Enter a Storage ID. 

4. In the Storage type field, enter SM_TRS_MSS. 

5. Click Confirm. 

6. Specify the required parameters: 

   • 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: Select Automatic, Native SQL, or Open SQL for query execution.

     • Automatic mode: Uses the DDIC table as a proxy object first. If that fails (due to SAP restrictions such as FLOAT/SSTRING keys or overly long keys), a DDIC structure is generated instead. This ensures Open SQL is used whenever possible for better performance, while falling back to Native SQL only when necessary.

   • DB Connection Name: Name of the existing SAP DBCO connection. Enter the name of the previously created DBCO connection. 

   • DB Schema (optional): 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: Switch between the column-oriented and row-oriented database management systems. Select this option if the target database is column-oriented. 

   • MSSQL Collation: Collation rules. Specify the MSSQL collation if a non-default value is required. To differentiate between uppercase and lowercase letters, enter Latin1_General_CS_AS in this field. For more information, see the SAP Support Portal and the page Case Sensitivity Issues in Primary Keys. 

   • Enable update: If selected, delta loads will be merged into existing data.

   • Map boolean to bit type: Select this option if you want to map SAP fields with the domains FLAG and XFELD to the MSSQL data type bit. 

7. Click Save. 

The new storage has been created. 

Open

Limitations

While you alter a table, only new fields can be added (characteristics or key figures). It is not possible to modify the data type or length of an existing field.