All Products
Search
Document Center

ApsaraDB RDS:Use the tds_fdw extension to query data of SQL Server instances

Last Updated:Dec 26, 2023

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

Prerequisites

  • The major engine version of the RDS instance is supported by the extension. For more information, see Extensions supported by ApsaraDB RDS for PostgreSQL.

  • 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.查看VPC网段

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

  1. 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.

  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 the schema of the foreign table:

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