In technical terms, Babelfish provides native, semantically correct execution of T-SQL over the TDS wire protocol, by default on port 1433, plus native SQL Server data types and runtime library support.

-Use cases: Migrating SQL Server to Aurora Postgres , Babelfish will readunderstand T-SQL

-Still rely on a conversion of schema and data to Postgres

SetUp Consideration 

-Minimum version  Aurora PostgreSQL (Compatible with PostgreSQL 13.4)

Differences between Aurora PostgreSQL with Babelfish and SQL Server

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/babelfish-compatibility.html

Creating an Aurora PostgreSQL cluster with Babelfish

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/babelfish-create.html

Connecting to a DB cluster with Babelfish turned on

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/babelfish-connect.html

Configuring a babelfish client

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/babelfish-connect-configure.html

When the Aurora PostgreSQL instance is set up with babelfish setup , proceed with a checkout:

1) Checkout 1 : Connect via a PostgreSQL endpoint . I use pgAdmin to connect ,

2)Checkpoint 2 : Connect via the SQL Server endpoint , by default 1433.

Example :

sqlcmd -S mybabelfish.cxxxxxxx.us-xxxxxxxx.amazonaws.com,myPortNumber -U sa -P myPassword

If you can’t connect – check the error message

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

A common problem is the access rules enforced by your local firewall and the IP addresses authorized to access your DB instance might not match. The problem is most likely the inbound rules in your security group.   By default – the Babelfish is set up using the default Parameter groups , specifying the babelfishpg_tds.port value as 1433.   If your firewall rules allow 1433 , than the problem is related to another issue – but it is typical that organisations will block 1433 as  part of any regular security lockdown.

When AWS RDS process builds an RDS instance it extracts the default values and creates a custom Parameter group titled similar to custom-aurora-postgresql13-babelfish-compat-xx. Within the Parameter group  look for the parameter babelfishpg_tds.port and edit the value to the port number configured for the firewall

Configuring a database for Babelfish

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/babelfish-configuration.html

Migrate from SQL Server to Amazon Aurora using Babelfish

https://aws.amazon.com/blogs/database/migrate-from-sql-server-to-amazon-aurora-using-babelfish/

Author: Jack Vamvas