How do I query data of an SQL Server database? - ApsaraDB RDS

This topic describes how to use the tds_fdw extension provided by PostgreSQL to query data of SQL Server instances.

Prerequisites

The RDS instance runs PostgreSQL 11 or a later version.
The minor engine version of the RDS instance is updated if the major engine version of the RDS instance meets the requirements but the extension is still not supported. For more information, see Update the minor engine version.
You must add the CIDR block of the virtual private cloud (VPC) in which your RDS instance resides to a whitelist of the SQL Server instance that you want to connect. Example: 172.xx.xx.xx/16.
Note
You can view the CIDR block of the VPC in which the RDS instance resides on the Database Connection page of the ApsaraDB RDS console.

Background information

The tds_fdw extension is a Foreign Data Wrapper (FDW) provided by PostgreSQL. You can use the extension to connect with foreign instances such as Microsoft SQL Server instances that use the Tabular Data Stream (TDS) protocol.

For more information, see postgres_fdw.

Create the tds_fdw extension

After you connect to an SQL Server instance, execute the following statement to create the tds_fdw extension:

create extension tds_fdw;

Use the extension

Execute the following statement to create a server:
```
CREATE SERVER mssql_svr
  FOREIGN DATA WRAPPER tds_fdw
  OPTIONS (servername '<Endpoint used to connect to the SQL Server instance>', port '<Port used to connect to the SQL Server instance>', database 'tds_fdw_test', tds_version '7.1');
```
Note
You must set the servername parameter to the internal endpoint that is used to connect to the SQL Server instance and the port parameter to the internal port that is used to connect to the SQL Server instance.

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

Specify the table_name parameter and execute the following statement to create a foreign table:

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

Specify the schema_name and table_name parameters and execute the following statement to create a foreign table:

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

Specify the query parameter and execute the following statement to create a foreign table:

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

Specify a foreign column name and execute the following statement to create a foreign table:

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');

Execute the following statement to create a user mapping:

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

Execute the following statement to import the schema of the foreign table:

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