Recently the news broke that Amazon decided to release the source code for "Babelfish for PostgreSQL", proposing an extension to the PostgreSQL database with the implementation of specific capabilities of the Microsoft SQL Server database.
The key goal of the project is to provide the ability to run applications written for SQL Server on servers running PostgreSQL. The project code is open under Apache 2.0 licenses and PostgreSQL License.
For those unfamiliar with Babelfish, you should know that this supports the network protocol used to connect clients to SQL Server, T-SQL, and SQL Server-specific query language extensions, allowing you to translate running applications from Microsoft SQL Server to PostgreSQL without modifying your code or with minimal changes and without replacing drivers to the DBMS. For applications, Babelfish looks like a normal SQL server. The project is already in use on Amazon Aurora.
Today, we make Babelfish available for Aurora PostgreSQL. Babelfish enables Amazon Aurora PostgreSQL-Compatible Edition to understand the SQL Server connection protocol. It enables you to migrate your applications from SQL Server to PostgreSQL cheaper, faster, and with less risk associated with such a change.
It is stated that the project Babelfish follows an open development model that enables community contributors to make change and influence development. For users, the change to Babelfish will allow you to avoid significant royalties for using SQL Server, bypass license restrictions and not depend on changes in the maintenance policy of a proprietary product (cancellation of benefits, termination of platform support, price increases).
The project includes a set of PostgreSQL extensions, a set of PostgreSQL patches, and a compass toolkit:
The extensions provide PostgreSQL support for additional syntax, data types, and functions required to migrate from SQL Server. A total of 4 extensions are proposed:
- babelfishpg_tsql: which is an extension to carry out an implementation of the T-SQL (Transact-SQL) language that augments SQL with procedural programming, support for local variables and advanced functions to handle strings, dates and mathematical expressions. Most of the requested T-SQL functions have been implemented, including save points, stored procedures, and nested transactions. However, a significant part of the syntax and functions remains unrealized (as a rule, these are characteristics that are rarely used in real applications, a compatibility table). For example, the constructs "ADD SIGNATURE", "CREATE AGGREGATE", "CREATE / ALTER / DROP APPLICATION ROLE | ASSEMBLY | ASYMMETRIC KEY", "BEGIN CONVERSATION TIME", "END / MOVE CONVERSATION", and so on. they are not yet available.
- babelfishpg_tds: an extension for the implementation of the TDS (Tabular Data Stream) protocol used in SQL Server for the network connection between the DBMS and the client. TDS protocol version 7.1 and higher is supported.
- babelfishpg_common: an extension for the implementation of specific SQL Server data types.
babelfishpg_money: is a money type implementation based on the FixedDecimal extension code.
The patches include changes to the PostgreSQL engine necessary to provide additional functionality to the Babelfish extensions. Currently, together with the PostgreSQL developers, work is being done to include the prepared patches in the main PostgreSQL composition. Patches prepared for PostgreSQL 13.
The compass utility is designed to parse T-SQL DDL scripts and SQL code for compatibility with Babelfish. In the output, a report is generated which functionality required to start the application is not yet supported by Babelfish.
The following client libraries for connecting to MS SQL Server are officially supported:
- OLEDB / MSOLEDBSQL provider
- OLEDB / SQLOLEDB driver
- Ado.NET entity framework
- SQL Server 11.0 Native Client
- ODBC (Open Database Connectivity)
JDBC (Java Database Connectivity)
Finally if you are interested in knowing more about it, you can check the details of the note In the following link. As for those interested in being able to see the source code, they can do so from this link.