Towards cloud-native databases, goodbye to Microsoft SQL Server, and welcome to Babelfish

Many of our customers have expressed a desire not to work with a commercial database vendor to avoid costly costs and onerous licensing terms . However, migrating from legacy commercial databases can be time- and resource-intensive. When migrating databases, use the Amazon Schema Conversation Tool and Amazon Database Migration Service to automatically migrate database schemas and data. However, migrating the application itself requires more work, including rewriting the application code that interacts with the database. Proactivity is sufficient, but cost and risk are often limiting factors.

Today, Babelfish for Aurora PostgreSQL is available. With Babelfish, the Amazon Aurora PostgreSQL Compatible Edition will be able to understand the SQL Server wire protocol. This tool allows you to migrate SQL Server applications to PostgreSQL at a lower cost, faster, and with less risk associated with the migration.

Applications can be migrated in a fraction of the time required for traditional migrations. You will continue to use the existing queries and drivers that your application currently uses. Simply point your application to your Babelfish-activated Amazon Aurora PostgreSQL database. Using Babelfish, Amazon Aurora PostgreSQL will understand the SQL Server wire protocol Tabular Data Stream (TDS) and enable PostgreSQL to understand common T-SQL commands used by SQL Server. Support for T-SQL includes elements such as SQL dialects, static cursors, data types, triggers, stored procedures, and functions. Babelfish significantly reduces the number of changes required to an application, thereby reducing the risks associated with database migration projects. Using Babelfish saves licensing costs when using SQL Server. Amazon Aurora can achieve the security, availability, and reliability of commercial databases at one-tenth the cost of commercial databases.

While SQL Server has been around for over 30 years, we don't expect it to support all features anytime soon. However, we will focus on the most common T-SQL commands and return the correct response or error message. For example, the MONEY data type has different characteristics in SQL Server (four decimal places of precision) and PostgreSQL (two decimal places of precision). This slight difference can lead to rounding errors and have significant implications for downstream processes such as financial reporting. In this case, and many others, Babelfish will ensure that the semantics and T-SQL functionality of SQL Server datatypes are preserved: we created a MONEY datatype that behaves as required by SQL Server applications. When you create a table with this data type through a Babelfish connection, you will get this compatible data type and behavior as required by SQL Server applications.

Create a Babelfish cluster using the console

To demonstrate how Babelfish works, we first connect to the console and create a new Amazon Aurora PostgreSQL cluster. The process is no different from that of a regular Amazon Aurora database. In the RDS Launch Wizard, first make sure to select an Aurora version or newer that is compatible with PostgreSQL 13.4. The updated console has additional filters to help select Babelfish compatible versions.

Then, down the page, select the option to open Babelfish.

In the Monitoring section, also make sure Enable Enhanced Monitoring is turned off. This option requires additional IAM permissions and preparation (not covered in this demo).

After a few minutes, the cluster is created with two instances, a writer and a reader.

Create a Babelfish cluster using the CLI

Alternatively, the cluster can be created using the CLI. First create the parameter group to activate Babelfish (the console will do it automatically):

Zsh

 aws rds create-db-cluster-parameter-group             \
    --db-cluster-parameter-group-name myapp-babelfish \
    --db-parameter-group-family aurora-postgresql13   \
    --description "babelfish APG 13"
aws rds modify-db-cluster-parameter-group             \
    --db-cluster-parameter-group-name myapp-babelfish \
    --parameters "ParameterName=rds.babelfish_status,ParameterValue=on,ApplyMethod=pending-reboot" \

Then create the DB cluster (adjust the security group ID and subnet group name when using the command below):

Zsh

 aws rds create-db-cluster \
    --db-cluster-identifier awsnewblog-cli-demo \
    --master-username postgres \  
    --master-user-password Passw0rd \
    --engine aurora-postgresql \
    --engine-version 13.4 \
    --vpc-security-group-ids sg-abcd1234 \
    --db-subnet-group-name default-vpc-1234abcd \
    --db-cluster-parameter-group-name myapp-babelfish
{
    "DBCluster": {
        "AllocatedStorage": 1,
        "AvailabilityZones": [
            "us-east-1c",
            "us-east-1d",
            "us-east-1a"
        ],
        "BackupRetentionPeriod": 1,
        "DBClusterIdentifier": "awsnewblog-cli-demo",
        "Status": "creating",
        ... <redacted for brevity> ...
    }
}

After the cluster is created, create the instance using the following command

Zsh

 aws rds create-db-instance \
    --db-instance-identifier myapp-db1 \
    --db-instance-class db.r5.4xlarge \
    --db-subnet-group-name default-vpc-1234abcd \
    --db-cluster-identifier awsnewblog-cli-demo \
    --engine aurora-postgresql
{
    "DBInstance": {
        "DBInstanceIdentifier": "myapp-db1",
        "DBInstanceClass": "db.r5.4xlarge",
        "Engine": "aurora-postgresql",
        "DBInstanceStatus": "creating",
        ... <redacted for brevity> ...

Connect to Babelfish cluster

Once the cluster and instance are ready, connect with the writer instance to create the database itself. You can connect to the instance using SQL Server Management Studio (SSMS) or other SQL clients such as sqlcmd . Windows clients must be able to connect to the Babelfish cluster, ensuring that the RDS security group authorizes connections from Windows hosts.

When using SSMS on Windows, select New Query in the toolbar and enter the database DNS name as the server name. Select SQL Server Authentication and enter the database login and password. Click Connect.

Important: Do not connect through the SSMS Object Explorer. Be sure to connect using the query editor via the new query button. Currently, Babelfish supports the Query Editor, but not the Object Explorer.

Once connected, check the version using the select @@version statement, then click the green Execute button in the toolbar. Read the statement check results at the bottom of the screen.

Finally, use the create database demo statement to create the database on the instance.

By default, Babelfish runs in single database mode. In this mode, each instance can have at most one user database. This situation allows a tight schema name mapping between SQL Server and PostgreSQL. Alternatively, multi-database mode can be turned on when the cluster is created. This action allows you to create multiple user databases for each instance. In PostgreSQL, user databases are mapped to multiple schemas, prefixed with the database name.

run the application

To demonstrate, we will use the database schema provided by SQLServerTutorial.net as part of their SQL Server tutorial to create the schema and populate it with data. The SQL script and application C# code used in this demo can be found in my GitHub repository . Many thanks to colleague Auja for the C# demo application.

In SQL Server Management Studio, open the create_objects.sql script and select the green execute icon on the top toolbar. A confirmation message appears indicating that the database schema has been created.

Repeat this with the load_data.sql script to load the data in the newly created table. The data load takes a few minutes to run.

Now that the database is loaded, open the C# application developed by Auja for accessing the SQL Server database. Modify two lines of code:

• Line 12: Type the DNS name of the Babelfish cluster created earlier. Note that the DNS name of the "writing" node in the cluster is used.
• Line 15: Type the password you entered when you created the DB cluster.

It's that simple! No other modifications are required for this application. This code, written for querying and interacting with SQL Server, just works as-is on Aurora PostgreSQL using Babelfish.

Open Source Transparency

We decided to open source the technology behind Babelfish to create the Babelfish Open Source Project for PostgreSQL . It will use a permissive Apache 2.0 and PostgreSQL license, which means you can modify, tweak, or distribute Babelfish as you see fit. Over time, we're moving Babelfish to fully open development on GitHub , so it's been transparent from the start. Today, anyone, whether you're an Amazon Cloud Technologies customer or not, can use Babelfish instead of SQL Server to quickly, easily, and cost-effectively migrate applications to open source PostgreSQL. We believe that Babelfish will make PostgreSQL available to a wider range of customers and developers, especially those who originally wrote a large number of complex applications for SQL Server.

Availability
Babelfish for Aurora PostgreSQL is available in all publicly available Amazon Cloud Tech regions at no additional charge. Start your application migration today .

– seb

PS: If you want to know the origin of the name Babelfish, remember the answer is 42. (Or read this slightly longer answer .)

A variety of free packages, get started tutorials , click to learn more.