This topic describes the features, architecture, scenarios, and usage notes of Babelfish for ApsaraDB RDS for PostgreSQL instances.

Definition

ApsaraDB RDS for PostgreSQL provides the Babelfish feature that is developed based on the Babelfish for PostgreSQL open source project. You can enable Babelfish when you create an ApsaraDB RDS for the PostgreSQL instance. After you enable Babelfish, your RDS instance can query and process data from both Microsoft SQL Server databases and PostgreSQL databases. This way, your RDS instance can parse and execute Transact-SQL (T-SQL) statements.

Babelfish supports Tabular Data Stream (TDS), the SQL Server wire-protocol and T-SQL, the Microsoft SQL Server query language. You can migrate the database of your application from an SQL Server instance to an RDS instance for which Babelfish is enabled by modifying only a few lines of code. You do not need to switch database drivers or rewrite SQL statements.

Scenarios

  • You want to reduce the license costs of SQL Server.
  • You want to migrate an SQL Server database to a PostgreSQL database. However, you do not want to spend a long period of time or efforts in modifying the application code.
  • You want to use the open source plug-in libraries of PostgreSQL, such as the PostGIS plug-in for spatio-temporal data analysis and the TimescaleDB plug-in for time series data analysis.
  • You want to reduce the costs by using a single RDS instance to process data from both PostgreSQL databases and SQL Server databases.

Architecture

Babelfish architecture
Architecture description:
  • An RDS instance for which Babelfish is enabled uses the following listeners to monitor requests from SQL Server clients and PostgreSQL clients over TCP ports:
    • TDS listener: receives requests over the TDS port for SQL Server. The default port number is 1433.
      Note Babelfish supports TDS 7.1 or later. Microsoft SQL Server 2000 and later versions are supported.
    • PostgreSQL listener: receives requests over the port for PostgreSQL. The default port number is 5432.
      Note RDS instances that run PostgreSQL 13 or 14 are supported.
  • When an RDS instance for which Babelfish is enabled receives a request from the TDS listener, the RDS instance forwards the request to the T-SQL parser. Then, the T-SQL parser converts the T-SQL statements of SQL Server to an execution plan that can be processed by PostgreSQL.
  • When an RDS instance for which Babelfish is enabled receives a request from the PostgreSQL listener, the RDS instance forwards the request to the PostgreSQL parser to generate an execution plan.
  • The SQL executor of PostgreSQL processes and executes all plans.

If you enable Babelfish for your RDS instance, the RDS instance incorporates the capabilities of both the PostgreSQL database engine and the SQL Server database engine. Your RDS instance can process requests from SQL Server clients and PostgreSQL clients. This helps reduce costs and enhance processing capabilities.

Migration modes

A PostgreSQL database named babelfish_db is provisioned for an RDS instance for which Babelfish is enabled. All migrated SQL Server objects and schemas are stored in this PostgreSQL database.

Note If you connect your application to your RDS instance over the TDS port, the babelfish_db database is invisible.

You can use the Single-DB mode or Multi-DB mode. The mode that you choose to use affects the names of schemas in SQL Server databases and those in the babelfish_db database.

Single-DB mode

  • Architecturesingle-db
  • Description: In this mode, the schema names of the user database in the babelfish_db database are the same as the schema names in SQL Server databases.
    For example, you create a database named DB_A over the TDS port and then create a schema named schema_A in the DB_A database.
    • The schemas named dbo and schema_A reside in the babelfish_db database of PostgreSQL.
    • The schemas named dbo and schema_A reside in the DB_A database of SQL Server.

Multi-DB mode

  • ArchitectureMulti-DB
  • Description: In this mode, the schema names of the user database in PostgreSQL are named in the format of dbname_schemaname. In SQL Server, the schema names remain unchanged.
    For example, you create a database named DB_A over the TDS port and then create a schema named schema_A in the DB_A database.
    • The schemas named DB_A_dbo and DB_A_schema_A reside in the babelfish_db database of PostgreSQL.
    • The schemas named dbo and schema_A reside in the DB_A database of SQL Server.

Pricing

The pricing of an RDS instance for which Babelfish is enabled is the same as the pricing of a regular RDS instance. No additional fees are charged. The price varies based on the instance configurations, such as the region, instance type, and storage type. For more information, visit the ApsaraDB RDS buy page.

Usage notes

  • You can enable Babelfish for an RDS instance only when you create the instance. You must use the following parameter settings to create the RDS instance:
  • Babelfish cannot be enabled for an existing RDS instance that runs PostgreSQL 13 or 14 and uses a new general-purpose instance type.
  • After Babelfish is enabled, you cannot disable it.

References