SQL Server on Linux: Canonical offers official support, AWS Babelfish helps users move to Postgres

Amazon tech requires fork of PostgreSQL, is not fully compatible with SQL Server

Canonical is offering joint support with Microsoft for SQL Server on Ubuntu running on Azure, all while Amazon is nudging users towards PostgreSQL with general availability of the Babelfish compatibility extension, now open source.

SQL Server for Linux was introduced over five years ago and has been generally available for four years so is no longer a novelty.

In 2018, Microsoft's general manager of Azure Data called SQL Server on Linux with embedded R and Python the "most successful [SQL] server product ever." Microsoft said that "the core Database Engine for SQL Server is the same on Linux as it is on Windows" though there are some features missing, including merge replication, FileStream, Stretch DB (dynamically extend data to Azure), database mirroring, and extras like SQL Server Agent, Analysis Services, Reporting Services and Data Quality Services.

Linux has advantages too, though, such as being able to use Linux containers on Kubernetes, consistency if the rest of the infrastructure is Linux, and generally lower licensing costs, though the cost for SQL Server itself is the same.

Performance? Attempts have been made to compare performance on similar hardware and generally conclude that differences are not dramatic.

Canonical has now introduced instances of Ubuntu Pro 20.04 or 18.04 (both LTS editions) for Azure, with SQL Server 2017 or 2019, offering joint support with Microsoft, up to 10 years of maintenance updates, and compliance with standards including FedRAMP, HIPAA, and PCI. Users could previously find SQL Server on Ubuntu in the Azure marketplace, but these are Microsoft images rather then Canonical's Ubuntu Pro which is a hardened configuration with automatic security updates and Kernel Livepatch, which the company describes as "kernel patches are delivered immediately, without the need to reboot."

The new images also get the benefit of the XFS file system and persistent memory (PMEM) when available. There are images for Web, Standard and Enterprise editions of SQL Server.

Babelfish makes PostgreSQL somewhat compatible with SQL Server data and applications

What about not using SQL Server at all? This may be an option even for those with applications that use T-SQL, the SQL Server flavour of SQL, and TDS (Tabular Data Stream), the protocol of SQL Server.

Amazon has announced general availability of Babelfish for Aurora PostgreSQL, which enables compatibility with databases and applications built for SQL Server.

Complete compatibility? Unfortunately not. This page describes limitations, and clarifies that "Babelfish doesn't offer complete support for T-SQL".

Amazon suggests that users come up with a blend of T-SQL and PostgreSQL in order to fill gaps like unsupported GROUP BY clauses, JSON support, XML support, the Geography type, and full-text search. The list of unsupported or partially supported T-SQL functions is long, having said which, SQL Server is a large product and many users only tap into a small part of its functionality.

The AWS commercial offering of Babelfish is only for its Aurora relational database service; but the company has also placed the Babelfish code on GitHub with an Apache 2.0 license or, for the PostgreSQL code, the PostgreSQL license.

Marc Linster, CTO at Postgres company EDB, told us that the Babelfish project is "a strategic and important move by AWS. It has confirmed our longstanding belief that the world is moving away from legacy databases and towards Postgres." He added that he is glad to see AWS contributing to the open source Postgres project.

One snag with Babelfish is that it requires a fork of PostgreSQL. "There are ongoing efforts to incorporate Babelfish hooks into PostgreSQL. In the meantime, a separate code tree will be available separate from the extensions, with all the hooks built into it," says the documentation.

This also means that users cannot simply add Babelfish extensions to an existing PostgreSQL installation. Instead, users of the open source Babelfish must build and install Postgres modified for Babelfish. Instructions are provided for Ubuntu 20.04 or Amazon Linux 2. Going this route though means freedom from both licensing costs for SQL Server and the running costs for Aurora PostgreSQL.

SQL Server licensing is expensive and this, combined with other advantages of open source, makes Babelfish an interesting proposition. The potential effort and uncertainty involved in porting an existing application, and needing to use a forked version of PostgreSQL, counts against it. Porting to native PostgreSQL will be more effort but might be more beneficial long term. ®