(SM-2402) Azure Synapse SQL

Table of Contents:

Storage management supports Synapse Dedicated SQL Pools in Azure. Storage Management automatically creates Synapse tables with round-robin distribution and can load them with data when used in combination with ADLS Gen2. It can also be used to query data back to SAP.

General Prerequisites

SAP NetWeaver release

Storage management requires SAP NW 7.01 SP15 or higher.

Open Ports

In a controlled network environment, it is common to have firewall rules in place. To enable communication between SAP systems and Azure, outbound communication from the SAP system to the following ports on the Azure side needs to be allowed:

Port

Type

AWS service

Port

Type

AWS service

1433

TCP

Synapse Dedicated SQL pool endpoint

80/443

HTTP/HTTPS

ADLS Gen2 endpoint

Storage Management allows encrypted communication through the public internet with Azure services, but for production deployment, it is recommended to have some kind of secure connectivity in place (VPN, Private Link, Private endpoints).

Java connector

Java connector is a critical middle-ware component used for processing SQL statements as well as facilitating communication with selected file storage services. Follow the steps described in the article Java Connector Setup to set it up before you continue.

The minimum required Java connector version is 222.

and library version supporting the msal4j library (optional for OAuth 2.0 Authentication).

ADLS Gen 2 storage

ADLS storage is required as storage for temporary data. Follow this guide to create the storage Azure Data Lake Gen2.

When you use OAuth2 authentication for ADLS, it can be reused also for Synapse.

Synapse storage

You need to create resources on Azure and set up authentication and authorizations. After that, you need to set connection details in Storage Management.

Create a Dedicated SQL pool

To get connectivity details for your SQL pool, enter your SQL pool Azure resource and go to Connection Strings.

Write down the above-highlighted information from the JDBC connection string: server hostname, database, and the user which will be used in the SM storage definition.

Create Schema (optional)

You can create a custom schema, otherwise, DBO is used.

Create DB user

a) OAuth 2.0 authentication

To use OAuth authentication using the same user that is set in the ADLS Gen2 storage, you need to add the user to Synapse and give him privileges for the database.

CREATE USER [app-glue-synapse-connector] FROM EXTERNAL PROVIDER; // app-glue-synapse-connector is an example Azure AD app. registration EXEC sp_addrolemember 'db_owner', 'app-glue-synapse-connector';

b) Database user authentication

The other option is to use authentication using standard database users. You can use the built-in admin created during pool creation, or a dedicated technical user created later on.

// switch to Master database CREATE LOGIN "app-datavard-connector" WITH PASSWORD = 'FovAy>pCYW%%WX'; // create your technical user and password //switch to the working database CREATE USER "app-datavard-connector" FOR LOGIN "app-datavard-connector"; EXEC sp_addrolemember 'db_owner', 'app-datavard-connector';

Download the JDBC driver for the SQL server

Download the JDBC driver from the MS JDBC Download site. Upload the .jar file to the SAP application server at /sapmnt/<SID>/global/security/dvd_conn/mssql/
The file needs to be owned by <sid>adm:sapsys

$ ls -ld /sapmnt/DVQ/global/security/dvd_conn/* drwxr-xr-x 2 dvqadm sapsys 4096 --- /sapmnt/DVQ/global/security/dvd_conn/mssql $ ls -l /sapmnt/DVQ/global/security/dvd_conn/mssql drwxr-xr-x 2 dvqadm sapsys 4096 --- /sapmnt/DVQ/global/security/dvd_conn/mssql/mssql-jdbc-9.2.1.jre15.jar

Create Synapse storage in Storage Management

You can choose between two authentication methods: database user/password, and oAuth2.0 credentials. For production environments, it is recommended to use oAuth.

Go to transaction /DVD/SM_SETUP
Create new storage of type AZURE_SDSP

a) oAuth 2.0 Authentication

Referenced storage

Already created referenced storage Azure Data Lake Gen2

Java connector RFC

RFC referencing the Java connector

Java call repeat

Number of times failed calls should be retried

Repeat delay (seconds)

Delay between retried calls

Dedicated SQL endpoint

Taken from JDBC database connection strings

Database name

Taken from JDBC database connection strings

Database schema

Existing schema in target DB (default is dbo)

Enable update

Not in use (for future implementation)

Use extended escaping

Not in use (for future implementation)

Driver path

Path to the JDBC driver (mssql-jdbc-9.2.0.jre8.jar)

Login timeout (seconds)

Maximum time in seconds in which the JDBC driver is trying to establish a connection

Fetch Size

Default size of fetch rows from the server using by JDBC driver

Hints

Additional string added to connection string when JDBC driver establishes a connection

OAuth 2.0 profile

Profile for OAuth 2.0 setup

b) JDBC user/password authentication

Go to transaction /DVD/SM_SETUP
Create new storage of type AZURE_SDSP

Referenced storage

Already created referenced storage Azure Data Lake Gen2

Java connector RFC

RFC referencing the Java connector

Java call repeat

Number of times failed calls should be retried

Repeat delay (seconds)

Delay between retried calls

Dedicated SQL endpoint

Taken from JDBC database connection strings

Database name

Taken from JDBC database connection strings

Database schema

Existing schema in target DB (default is dbo)

Enable update

Not in use (for future implementation)

Use extended escaping

Not in use (for future implementation)

Driver path

Path to the JDBC driver (mssql-jdbc-9.2.0.jre8.jar)

Login timeout (seconds)

Maximum time in seconds in which the JDBC driver is trying to establish a connection

Fetch Size

Default size of fetch rows from the server using by JDBC driver

Hints

Additional string added to connection string when JDBC driver establishes a connection

Username

If using JDBC user/password authentication - username

Password

If using JDBC user/password authentication - password