AWS has come good on its December 2020 promise to open source a tool that lets users migrate SQL Server-based apps to the free and opensource PostgreSQL database with minimal drama, much to the delight of many in the industry -- a step that should prove of considerable interest to enterprise users otherwise locked into SQL Server, a commercial offering from Microsoft used by 98 of the Fortune 100 that can rapidly get costly at scale.
Babelfish for PostgreSQL, released on October 28, 2021 under an open Apache 2.0 licence, lets PostgreSQL understand queries from applications written for Microsoft SQL Server, via a set of extensions that provide T-SQL (Microsoft's proprietary extension to SQL) capabilities, and an endpoint that understands the SQL Server wire protocol Tabular Data Stream (TDS) as Postgres enhancements; simply translated, apps written for SQL Server can now work with PostgreSQL with fewer code changes and no need for changes to database drivers.
That, at least, is the big idea...
See also: AWS is building a “new storage backend for Amazon S3” on 40,000 lines of Rust: Here’s what you need to know
AWS, long seen by many as something of a bête noire of the open source world, has been working hard to improve its relationship with the opensource world. It promised this week that it "will not tweak the software so that it runs better for any vendor (including AWS) at the expense of others; if this happens, call it out and we will fix it as a community" -- although the release does make it easy to migrate applications built on SQL Server, the relational database management system developed by Microsoft, onto its own Amazon Aurora cloud database offering.
Users, in short, can try the AWS-hosted Aurora version -- or use the open source code to migrate existing SQL Server-underpinned applications to any other cloud-based DBaaS Postgres offering, on-premises or bare metal Postgres alternative. (Postgres, incidentally, was voted the "most wanted" database by over 70,000 developers responding to the 2021 Stack Overflow survey, just ahead of MongoDB, so the release will be popular.)
Migrate SQL Server to PostgreSQL with Babelfish
Current Babelfish for PostgreSQL support for T-SQL includes elements such as the SQL dialect, static cursors, data types, triggers, stored procedures, and functions but it's not ready to just seamlessly point-and-schlurp (technical term) at complex existing SQL Server-based applications quite yet.
(DBAs and others familiar with SQL Server and taking a close look at Babelfish can see a few key variations to look out for here. Early FAQs are here.)
As AWS's Sébastien Stormacq noted in a blog on the release: "SQL Server has evolved over more than 30 years, and we do not expect to support all functionalities right away. Instead, we focused on the most common T-SQL commands and returning the correct response or an error message."
He added: "For example, the MONEY datatype has different characteristics in SQL Server (with four decimals precision) and PostgreSQL (with two decimals precision). Such a subtle difference might lead to rounding errors and have a significant impact on downstream processes, such as financial reporting.
"In this case, and many others, Babelfish ensures the semantics of SQL Server data types and T-SQL functionality are preserved: we created a MONEY datatype that behaves as SQL Server apps would expect. When you create a table with this datatype through the Babelfish connection, you get this compatible datatype and behaviors that a SQL Server app would expect.
One database administrator (DBA) team lead at a multinational told The Stack: "This looks good as a cheaper alternative to MSSQL. The only thing I always say to our dev teams when they download these tools is to check it on an isolated server first; you never know what people have changed! Do as much testing as possible. Sometimes these [open source] tools do not suit all estates and you might get issues trying to migrate a big database. But I'd certainly be interested to see them try this with a few small databases that are easy recoverable..."
Planning to migrate SQL Server applications to PostgreSQL? Like what you see? Concerned at omissions? We'd like to hear your views on Babelfish for PostgreSQL. Pop us an email.
