Introduction

As organizations migrate their SAP ASE databases to AWS, they often find themselves on a journey of rehosting and refactoring. The advantages of transitioning to open-source databases are compelling, but the process can be time-intensive and may require a staged migration approach. Consequently, more customers are running their SAP ASE databases in the cloud alongside modern open-source databases like Aurora PostgreSQL and RDS PostgreSQL.

For these customers, integrating SAP ASE data into their broader organizational data ecosystem is crucial to fully leverage the cloud, support application integration, and meet various data analytics requirements.

This post demonstrates how to unlock data in SAP ASE and integrate it with Aurora PostgreSQL and RDS PostgreSQL using the tds_fdw extension, which facilitates access to databases that support the Tabular Data Stream (TDS) protocol, such as SAP ASE. This integration opens up numerous possibilities, including the creation of modern data lakes, which will be explored in subsequent posts. In this post, however, we will focus on setting up the extension and establishing connectivity between SAP ASE and Aurora PostgreSQL.

In this post we will create the below final state allowing us to access and unlock the data available in the existing SAP ASE.

Data Federation Options

For SAP ASE users, Foreign Data Wrappers (FDW) in PostgreSQL serve a similar purpose to SAP ASE's Component Integration Services (CIS) feature. SAP ASE CIS enables local access to "remote" objects, whether they are homogenous (e.g., SAP ASE) or heterogeneous (e.g., Oracle, Microsoft SQL Server) database engines.

Similarly, in Aurora PostgreSQL, the FDW feature is used to access data stored in external servers. There are two options in PostgreSQL when accessing remote objects. PostgreSQL also offers the dblink module for accessing remote data, dblink is not standards-compliant and has been largely superseded by FDWs, which use modern, standards-compliant syntax and generally provide better performance. Therefore, it is best practice to use FDWs.

Several FDWs are available to connect PostgreSQL to various remote data stores, ranging from other SQL databases to flat files. Most FDWs are independent open-source projects implemented as PostgreSQL extensions and are not officially supported by the PostgreSQL Global Development Group.

One notable FDW is postgres_fdw, which comes with the PostgreSQL source as a contrib extension module. postgres_fdw enables federated query capabilities, allowing interaction with any remote PostgreSQL-based database, whether managed, self-managed on Amazon EC2, or on-premises. This extension is available in all supported versions of Amazon RDS for PostgreSQL and Aurora PostgreSQL.

While SAP ASE allows cross-database object access on the same server without requiring CIS, access to remote server objects does require CIS setup. In contrast, PostgreSQL requires the setup of the postgres_fdw module to access objects in another database, whether on the same or a different server. This distinction in architecture means that if there is significant cross-database access, you might consider consolidating separate databases into separate schemas within a single PostgreSQL database. Tools like the Schema Conversion Tool can facilitate mapping and migrating multiple databases into separate schemas within a single PostgreSQL database.

Foreign Data Wrappers (FDWs) are made available through extensions, and Aurora PostgreSQL supports several FDW targets. For example, the oracle_fdw extension allows access to Oracle databases. For our use case and this post, we will focus on using the tds_fdw extension. The PostgreSQL tds_fdw extension allows access to databases that support the Tabular Data Stream (TDS) protocol, such as Sybase and Microsoft SQL Server databases. This FDW enables connections from your Aurora PostgreSQL DB cluster to databases that use the TDS protocol, including SAP ASE. For more details, refer to the tds_fdw documentation on GitHub. The tds_fdw extension is supported on Amazon Aurora PostgreSQL version 13.6 and higher.

Prerequisites

In this post, we will demonstrate the creation of a Foreign Data Wrapper (FDW) from Aurora PostgreSQL to access an SAP ASE (Sybase) database. This tutorial assumes that you have already installed SAP ASE and Aurora PostgreSQL version 13.6 or higher, and that these two servers are connected to each other. We will use DBeaver as our database client to connect to both Aurora PostgreSQL and SAP ASE, as illustrated below.

1.   Create the local and remote databases

Next, create a target table with data. This table will be created in the targetdb database in SAP ASE, and Aurora PostgreSQL will access it through the FDW. To do this, click on the targetdb console tab in DBeaver and run the following SQL:

create database sourcedb;

As mentioned earlier, FDW functionality in PostgreSQL is provided through an extension. This extension is readily available in Aurora PostgreSQL without the need for installation; it simply needs to be created within a database. The following command will create the extension in the sourcedb:

create database targetdb
go

2.   Create TargetDB Data

Next, create a target table with data. This table will be created in the targetdb database in SAP ASE, and Aurora PostgreSQL will access it through the FDW. To do this, click on the targetdb console tab in DBeaver and run the following SQL:

create table employee( id int, name varchar(255), email varchar(255))
insert into employee values(1,'Peter','peter@bestcompany.com')
insert into employee values(2,'Sam','sam@bestcompany.com')
insert into employee values(3,'Jill','jill@bestcompany.com')
go

3.   Create FDW extension in sourcedb

As mentioned earlier, FDW functionality in PostgreSQL is provided through an extension. This extension is readily available in Aurora PostgreSQL without the need for installation; it simply needs to be created within a database. The following command will create the extension in the sourcedb:

create extension tds_fdw;

4.   Configure FDW

To access the objects in the target database, we first need to define the server object with the necessary connection details. The following command will create the server mapping with these connection details:

CREATE SERVER fdw_targetdb FOREIGN DATA WRAPPER tds_fdw
OPTIONS (
		servername 'ip-10-32-3-65.ap-southeast-2.compute.internal', 
		port '5000', 
		database 'targetdb'
	);

5.   Create User mappings

The CREATE USER MAPPING command defines a mapping of a user to a foreign server. In the following code, the user postgres is mapped to the foreign server fdw_targetdb (target database) using the postgres login. In this example, the source and target users are the same, but they do not have to be. If the users differ, you can specify them using this command as well.

CREATE USER MAPPING FOR postgres
SERVER fdw_targetdb
OPTIONS (username 'sa', password 'xx');

6.   Create remote definitions mappings

Next, we will create the table definition in sourcedb for the object that exists in targetdb.

create foreign table employee_fdw( id int, name text,email text)
server fdw_targetdb
OPTIONS( TABLE_NAME 'employee');

7.   Access Data

Now that all the setup is complete, let's quickly recap: we created a "remote server" by specifying the connection attributes, then created a user mapping that links the local user to the remote user (i.e., postgres/sa), and finally defined the table structure in sourcedb. If everything has been set up correctly, the following SELECT statement will allow Aurora PostgreSQL to access the data in SAP ASE.

select * from employee_fdw;

Conclusion

Using the tds_fdw extension, we successfully set up access to data from SAP ASE in Aurora PostgreSQL. This setup allowed us to view the data stored in the targetdb. The foreign table created with tds_fdw can be used in joins or other parts of a query alongside local tables, however, pushdowns in tds_fdw is limited. While this post focused on the use of SELECT statements, it's important to note that write statements are currently not supported by tds_fdw. However, remote procedure calls are possible.

To further enhance functionality and explore additional use cases, you can utilize other database objects, such as materialized views, to hydrate data lakes and various analytical stores . Stay tuned for the next post, where we will demonstrate an analytics use case.