How to access data in a SQL Server database - ApsaraDB RDS

Use the tds_fdw extension to access data in a SQL Server database.

Prerequisites

Your RDS PostgreSQL instance must run on major engine version 11 or later.
If an instance meets the major engine version requirement but does not support the extension, upgrade the minor engine version. For example, for an RDS PostgreSQL 17 instance, the minor engine version must be 20241030 or later. For instructions, see Upgrade the minor engine version.
Add the VPC CIDR block of your RDS PostgreSQL instance, such as 172.xx.xx.xx/16, to the IP address whitelist of the SQL Server instance.

Note
On the Database Connection page for your RDS PostgreSQL instance, confirm that the Network Type is VPC, note the VPC CIDR block, such as 172.x.x.x/16, and verify that the Intranet Port is 5432.

Background information

tds_fdw is a PostgreSQL foreign data wrapper that connects to databases that use the Tabular Data Stream (TDS) protocol, such as Microsoft SQL Server.

For details, see the official tds_fdw documentation.

Create the extension

Connect to your instance and run the following command:

create extension tds_fdw;

Use the extension

Create a foreign server. For example:
```
CREATE SERVER mssql_svr
  FOREIGN DATA WRAPPER tds_fdw
  OPTIONS (servername '<endpoint>', port '<port>', database 'tds_fdw_test', tds_version '7.1');
```
Note
In the server definition, servername must be set to the internal endpoint of the SQL Server, and port must be set to the internal port of the SQL Server.

Create a foreign table. You can use one of the following methods:

Use the table_name option. For example:

CREATE FOREIGN TABLE mssql_table (
 id integer,
 data varchar)
 SERVER mssql_svr
 OPTIONS (table_name 'dbo.mytable', row_estimate_method 'showplan_all');

Use the schema_name and table_name options. For example:

CREATE FOREIGN TABLE mssql_table (
 id integer,
 data varchar)
 SERVER mssql_svr
 OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');

Use the query option. For example:

CREATE FOREIGN TABLE mssql_table (
 id integer,
 data varchar)
 SERVER mssql_svr
 OPTIONS (query 'SELECT * FROM dbo.mytable', row_estimate_method 'showplan_all');

Map columns to remote column names. For example:

CREATE FOREIGN TABLE mssql_table (
 id integer,
 col2 varchar OPTIONS (column_name 'data'))
 SERVER mssql_svr
 OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');

Create a user mapping. For example:

CREATE USER MAPPING FOR postgres
  SERVER mssql_svr 
  OPTIONS (username 'sa', password '123456');

Import a foreign schema. For example:

IMPORT FOREIGN SCHEMA dbo
  EXCEPT (mssql_table)
  FROM SERVER mssql_svr
  INTO public
  OPTIONS (import_default 'true');