How Babelfish lets SQL Server migrations swim with the tide

Smoothing the path to a managed cloud database

Sponsored Feature Managing and maintaining a large SQL Server database in-house can be complex, and most database administrators (DBAs) will gratefully accept all the help they can get. Interest in switching to managed relational databases in the cloud is consequently on the rise as companies count the cost of cumbersome management tasks and hefty license fees. But there is still some concern over the cost and complexity of migration.

Last year, Amazon Web Services (AWS) launched Babelfish for Amazon Aurora PostgreSQL-Compatible Edition, a capability which smooths the transition between SQL Server and Amazon Aurora, a relational database management system (RDBMS) built for the cloud with full MySQL and PostgreSQL compatibility. So, what is Babelfish, how can it help ease SQL Server migrations, and why would you want to make the jump in the first place?

Database administrators (DBAs) have been wrestling with on-premises RDBMS systems for decades, but figures suggest that they are increasingly willing to move those workloads into the cloud to ease the administrative burden.

For example, according to a 2020 IDC survey of over 400 data professionals in North America, 68 percent believed the global pandemic had accelerated the migration of on-premises databases to the cloud. A similar number (63 percent) reported they were actively migrating data to the cloud, with almost a third (29 percent) planning to start doing so within three years.

The IDC Technology Spotlight report also noted how Babelfish enables applications and data to follow a quick and relatively easy migration path from on-premises to the cloud under a lift and shift model. That approach may also go some way to addressing any lingering apprehension over the amount of legwork involved in moving SQL Server off-premises.

Cloud migration options for Microsoft SQL Server

There are three ways to run SQL Server workloads at AWS. Option one is for customers to run their database on an EC2 Windows instance, providing their own license for SQL Server. Option two is to purchase Windows and SQL Server from AWS. Customers can either buy their licenses from AWS, or bring their own licenses, and they must manage the operating system and SQL Server themselves. This gives them some advantages, such as the avoidance of capital expense on their own servers and storage.

The third option is to run with AWS's managed service, the Amazon Relational Database Service (Amazon RDS) solution, which manages compute and storage, leaving customers free to focus on application optimization and business value. This includes a license for the software sourced by Amazon and is offered as part of its pricing.

However, many customers like the idea of a license-free open-source RDBMS engine in the cloud. This is where Aurora comes in. Launched in 2014 with MySQL compatibility, the managed service expanded with a PostgreSQL offering three years later. Aurora's open-source compatibility enabled AWS to dispense with licenses and offer it on a pay-as-you-go basis, charging for operation by the hour.

The pay-as-you-go model helps to reduce licensing costs, explains Richard Waymire, Senior Manager in Product Management for Aurora PostgreSQL at AWS. "The biggest impact is not being committed to a large deal if you move to RDS," he says. "You don't need to sign a multi-year agreement with minimum licensed cores."

Friction-free database management

As a former DBA, Waymire appreciates the appeal of jumping from an on-prem SQL Server to a managed system.

"There are things you have to do to manage and run a database that do not actually add any business value," he says. These include daily backup runs, including log backups, needed to support continuous recovery capabilities. On-prem SQL Server users must also ensure that the database software itself has the latest security and reliability updates.

AWS takes care of those tasks for Aurora users. This enables DBAs to focus on value-added work like updating schemas, optimizing for performance, building the appropriate indexes, and working with developers to tune their applications.

The convenience of a managed offering also comes with enhancements in transaction performance. "Migration from SQL Server to Aurora scales up for the right workloads," Waymire explains. The company allows customers to expand the available database computing power on demand. It also supports up to 15 read replicas accessing the same physical storage. These replicas allow applications to scale up their database reads on demand, avoiding database bottlenecks.

While organizations are interested in these benefits, they are still nervous about the cost and complexity of migrating. Unlike PostgreSQL, SQL Server uses a SQL dialect called T-SQL, along with its own wire-level protocol, Tabular Data Stream (TDS).

AWS Data Migration Service (AWS DMS) will support a migration for like to like deployments or to a different database, like SQL Server to Aurora. The cloud provider also offers the AWS Schema Conversion Tool (AWS SCT) to convert database schemas between different engines, but customers would still have to re-write SQL Server application code to work with Aurora PostgreSQL.

Babelfish swims into view

Amazon unveiled its solution to this problem at its re:Invent conference in December 2020. Babelfish accelerates migrations between SQL Server and Aurora PostgreSQL by taking care of conversions at the application level. The service itself is not a migration tool; Aurora PostgreSQL itself has been extended to understand the TDS specification that Microsoft released under its Open Specification Promise arrangement in 2008.

"A significant chunk of our business is commercial migrations," says Waymire. The goal is to let people move databases into PostgreSQL to stop paying licensing fees." Babelfish helps customers do that quickly without having to completely rewrite their applications. They can either continue running those applications with Babelfish or migrate them to native PostgreSQL applications in the future.

Babelfish eliminates differences when running SQL Server applications against Aurora PostgreSQL. For example, Aurora and SQL Server round to different levels of precision when using the native MONEY data type in each database. That could produce different results between applications addressing the two platforms. Babelfish added a new data type for MONEY that is compatible with the expectations of SQL Server users.

Pre-Babelfish, the typical migration workflow from SQL Server to Aurora PostgreSQL would involve running the AWS SCT to convert SQL Server's schema to PostgreSQL. After that, customers would use AWS DMS to port the data into Aurora, and subsequently validate that the database worked. Then came the real heavy lifting: rewriting their applications to be compatible with Aurora PostgreSQL, a process which still represents an appropriate migration technique for some customers.

With Babelfish, only the first two steps are necessary. Instead of rewriting, companies can simply point their applications at Babelfish and then test them. Babelfish launched with support for the most common database migration use cases, returning errors to the application if it did not support an operation.

AWS has spent the time since the original launch in 2020 expanding those migration use cases to support more operations and improve compatibility, meaning that developers might not need modifications at all, Waymire says.

Opening up more development choice

AWS intended Babelfish as a stepping stone for migration, enabling customers to get SQL Server applications running against Aurora PostgreSQL quickly. Once this is achieved they can develop their code natively against the managed PostgreSQL service rather than coding for T-SQL.

"You've made a migration decision to go to PostgreSQL, so don't prolong your time in that ecosystem," Waymire advises. "You can do that if your developers are not yet trained in open source technologies, but it creates additional technical debt."

Waymire hopes that customers will expand their reach into other database technologies where it makes sense. SQL Server licenses tend to keep the entire workload inside the relational model, but customers who pay as they go have more flexibility. They can split their post-migration workload between relational and NoSQL engines, he points out.

"If a customer isn't happy with a commercial licensing agreement it's often hard to change that," he says. The customer is normally locked in for a set period of time. "But with a managed service you can switch your workload between different technologies as necessary."

A customer moving from SQL Server might shift some of their data into Aurora PostgreSQL for example, but might prefer to move their mobile application functionality to Amazon's DynamoDB, which is key-value store. Because you are not locked into long-term licensing commitments, it is easy to mix and match.

Still a learning curve

Developers moving to a Babelfish-powered Aurora PostgreSQL platform can get up and running quickly, but Waymire warns that they must still make some adjustments. They must take time to understand the shared security responsibility model for example, ensuring that their applications are secure in the cloud.

Similarly, while AWS manages the database, it is still important to manage some tasks in-house such as testing stored procedures and practicing recovery regularly. They must also learn how to interact with a new database platform in areas such as tuning and optimization.

In spite of this learning curve, the cost and long-term management benefits for the service are clear. And Babelfish is not going away, Waymire says.

AWS released Babelfish to open source last year, fulfilling a promise made at launch time. This enables customers to extend it themselves. It is a clear sign that the company is committed to the little service that could, he concludes: "We're in Babelfish for the long haul."

Sponsored by AWS.