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

Prerequisites

  • Your ApsraDB RDS for PostgreSQL instance runs one of the following database engine versions:
    • PostgreSQL 12 with a minor engine version of 20200421 or later
    • PostgreSQL 11 with a minor engine version of 20200402 or later
    Note To view the minor engine version of your RDS instance, you must log on to the ApsaraDB RDS console and go to the Basic Information page. In the Configuration Information section of the page, you can check whether the Upgrade Kernel Version button is displayed. If the button is displayed, you can click the button to view and update the minor engine version of your RDS instance. If the button is not displayed, your RDS instance runs the latest minor engine version. For more information, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
  • You must add the CIDR block of the virtual private cloud (VPC) in which your RDS instance resides to an IP address whitelist of the SQL Server instance that you want to connect. Example of an CIDR block: 172.xx.xx.xx/16.
    Note You can view the CIDR block of the VPC in which an RDS instance resides on the Database Connection page of the ApsaraDB RDS console. The CIDR block of the VPC

Background information

The tds_fdw plug-in is a Foreign Data Wrapper (FDW) provided by PostgreSQL that you can use to connect with foreign instances such as Sybase and Microsoft SQL Server instances. These instances use the Tabular Data Stream (TDS) protocol.

For more information, see postgres_fdw.

Create a tds_fdw plug-in

After you have connected to an SQL Server instance, execute the following statement to create a tds_fdw plug-in:

create extension tds_fdw;

Use the tds_fdw plug-in

  1. Execute the following statement to create a server:
    CREATE SERVER mssql_svr
      FOREIGN DATA WRAPPER tds_fdw
      OPTIONS (servername '<The endpoint used to connect to the SQL Server instance>', port '<The 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 used to connect to the SQL Server instance and the port parameter to the internal port used to connect to the SQL Server instance.
  2. 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');
  3. Execute the following statement to create a user mapping:
    CREATE USER MAPPING FOR postgres
      SERVER mssql_svr 
      OPTIONS (username 'sa', password '123456');
  4. Execute the following statement to import a schema from a foreign table:
    IMPORT FOREIGN SCHEMA dbo
      EXCEPT (mssql_table)
      FROM SERVER mssql_svr
      INTO public
      OPTIONS (import_default 'true');