AWS Babelfish

What is AWS Babelfish?

AWS Babelfish is like an extended functionality added to Amazon Aurora PostgreSQL-Compatible Edition. Using AWS Babelfish, the hectic data migration process is simplified and automated to a great extent. Babelfish was designed to shift backend developers from Microsoft SQL servers to open-source database servers, particularly PostgreSQL.

Migrating database servers has been taboo for ages. Developers and Companies usually stick to a particular database as database migration consumes time and effort. In addition, it increases the cost involved as the entire application code needs to be rewritten.

AWS Babelfish emerges as a boon in solving the issues discussed above. Let's discuss some of the important features of AWS Babelfish which make it so useful.

• AWS Babelfish provides Aurora PostgreSQL the ability to understand T-SQL. T-SQL is an extension that adds extended functionality to SQL.
• Babelfish supports the same communication protocol used by the Microsoft MySQL server.
• Since the communication protocols are similar, the application/database code does not need to be modified much. Only minimal changes are required for unsupported features.
• Babelfish is an in-built part of Amazon Aurora without additional charges.

Benefits of AWS Babelfish

There are several benefits of using AWS Babelfish. Let's discuss some of them.

Reduce Migration Time

• AWS Babelfish is built in a way that makes Amazon Aurora PostgreSQL capable of understanding T-SQL. The communication protocols used with the help of Babelfish are similar to the MySQL server.
• The communication protocols are the same. Converting the database application code is easier. It can be altered partially; only some minimal changes are needed. This eventually reduces the time involved in migration.
• Babelfish eradicates the need to change the libraries and packages, saving the time taken in migration.

Lesser Migration Risk

• Usually, when the code is converted to another form, it must be tested again to ensure all the functionalities are working fine. In manual alterations, the chances of breakages are higher, involving a lot of code rework.
• Migration using AWS Babelfish needs only slight changes in the code.
• The lesser the alteration, the lesser the chances of functionality failures.

Cost-Effective

• The code rework is less automated, and the time taken is also less. No additional developers need to be hired to execute the process. All these save the cost involved in the migration process.
• Moreover, AWS Babblefish is an in-built Amazon Aurora functionality, so no additional cost is involved.

Easy to Use

• Using the Babelfish Compass tool, we can easily detect where the SQL statements need to be changed.
• It is very easy to debug once the areas are pointed out. The report generated by the Babelfish compass tool makes sure the SQL code is ready to be migrated to AWS Aurora PostgreSQL.

How AWS Babelfish Works?

Let's look at the diagram below and try to understand the working of AWS Babelfish.

• AWS Babelfish provides support for SQL server language.
• Any application written using T-SQL can be migrated to Amazon Aurora via the Babelfish.
• AWS Babelfish uses the Babelfish compass to decrypt SQL server code and generates a report pointing out the statements in the application code which need to be altered.
• Once the required changes are made and the Babelfish compass tool flags the application code as good enough, it can be used by Babelfish to migrate it to Amazon Aurora PostgreSQL.
• The application gets migrated to the Amazon Aurora PostgreSQL server in a convenient and automated way.

AWS Babelfish for PostgreSQL

AWS Babelfish for PostgreSQL holds the Aurora PostgreSQL DB cluster so that it accepts database connections from SQL Server clients. It provides an endpoint for an Aurora PostgreSQL database cluster that allows AWS Babelfish to understand the SQL Server wire-level protocol and SQL Server statements.

AWS Babelfish supports both SQL dialects(SQL Server dialect and PostgreSQL dialect) through their native wire protocols on ports:

• SQL Server dialect(T-SQL) uses port 1433 to connect to the clients.
• PostgreSQL dialect (PL/pgSQL) uses port 5432 to connect to the clients.

Refer this link to know more about Amazon Babelfish for PostgreSQL.

AWS Babelfish Instead of MySQL Server

Migrating from a commercial database to an open-source database is quite a difficult process. The intensive work and cost involved in the process have forced companies to stick to the commercial database for a long period until and unless the database migration increases productivity by up to 200 times. Here in this section, we will cover the steps to work with Babelfish to replace the MySQL database servers.

Creating Aurora Cluster/Babelfish Cluster using the Console

1. Log in to your AWS account and search for RDS in the search bar. Select the RDS service which shows up in the list.
2. You are redirected to the AWS RDS page. You can see a list of choices in the left navigation pane. Choose the Databases option.
3. On the next page, we will see the list of RDS databases if we have any. We do not have any database here, so let's create one by clicking on Create Database.
4. On the next page, you are asked to fill in some details required for creating a database. We will go with Standard Create. The engine option will be Amazon Aurora as we create an AWS Aurora Cluster.
5. For the edition, select Amazon Aurora PostgreSQL-Compatible Edition. In the dropdown of hidden filters, we need to ensure the option Show versions that support the Babblefish for PostgreSQL feature is enabled.
6. Select the latest version from the Available versions dropdown.
7. From the Templates, we can select Dev/Test as we are creating this for demo purposes only. Usually, companies opt for production templates. In the Settings section, give a name to your database, and update the admin name and password.
8. In the VPC security group, we can opt for the create new option to configure a rule separately for the amazon aurora cluster and add a name. Make sure the checkbox Turn on Babelfish is checked, as we are working with Babelfish as a part of this demo.
9. Other settings can be left as default. After filling in the required details, click the Create Database button.
10. Your cluster is created with an instance as a part of the cluster. It takes 2-3 minutes to completer the creation process, and then the instance and the cluster status change to available.

Setting up Connectivity

1. Select the Amazon Aurora Cluster to which you want to connect.
2. In the Connectivity and Security tab, which appears below, we can see the security group in an active state. Click on the security group to move to the security group page.
3. The security group is selected by default. From the actions dropdown, click on Edit Inbound Rules.
4. In the inbound rules, a default rule is listed. We will create a new rule by clicking on Add rule.
5. For the new rule, select the MSSQL option in the Type dropdown. The default protocol TCP and default port 1453 can be the same. Select the My IP option for the source, so the database is unavailable for public access.
6. Save the rules, and the inbound rules for your security group get updated. Now you can connect to your database easily.

Connect to the Babelfish Cluster

Follow these steps:

1. Open the SQL Server Management Studio.
2. Copy the writer instance endpoint name to connect to the server.
3. Using the username, password, and writer instance endpoint to connect to the Babelfish Cluster.
4. We connected successfully.

We successfully set up a database cluster with AWS Babelfish on the Aurora cluster.

How to Migrate from SQL Server to Amazon Aurora Using AWS Babelfish

Before learning to migrate the SQL server to Amazon Aurora Using AWS Babelfish, let's first understand the need to do this.

Challenges Faced in Database Migration

Usually, companies face many issues if they want to transfer/migrate their database from MySQL server to PostgreSQL. Some of the common issues include:

• Rewriting the application code manually.
• Ensuring the application behavior does not change.
• Reducing the errors which can occur as a consequence of the changes.
• The time involved in ensuring the changes are intact and working and the increased cost of maintaining developers to execute the migration process.
• Switching from a commercial SQL server to open-source databases is time-consuming and resource intensive.

Overview of Steps to Migrate Database using Babblefish

Babblefish resolves the above-discussed issues and handles database migration in an automated way, reducing the time consumed and cost involved. The steps for migrating the database are extensive, so let's discuss the steps to be followed briefly before proceeding.

1. The DDL (Data Driven Language) is generated from the source database.
2. Babblefish does not support SQL features, so the Babelfish Compass tool checks if the application contains any SQL features.
3. Remove any unsupported SQL features if detected by the Babblefish Compass tool.
4. Create a Babblefish cluster and connect it to the Babblefish database.
5. Run the updated DDL and test if the application is working as expected.

Now that we know the steps let's migrate the database using AWS Babblefish.

Generate DDL File for the Database

For demo purposes, click on this link, to use the DDL file. Copy the SQL file and save it with the name school_db with .sql extension.

Our DDL file is ready. Let's set up the Babelfish Compass.

Babelfish Compass Tool

One prerequisite is that we must have a JAVA/JRE version installed in your system. Click on this link to download, and your technique has no java runtime environment. After downloading, install it in the system.

Let's start to use the Babelfish tool to build the reports by following these steps:

• Click on this link to download the zip file of Babelfish Compass.
• Unzip the file and open the BabblefishCompass folder.
• Create a folder test where we can save the DDL file from the last step.
• Paste the DDL file into this folder.
• Open the BabelfishCompass folder in the command prompt.
• Run the following command to check it is working fine. BabelfishCompass.bat –help
• To analyze the DDL file, we must run the following command line: BabelfishCompass.bat YourFirstReportName path_to_the_DDL_file
• It will generate a report in HTML format which shows a summary of SQL features supported or unsupported by Babelfish.
• To generate a report to know the exact location of each non-supported feature, run the following command: BabelfishCompass.bat YourFirstReportName -reportoption xref.
• It will generate a report in HTML format and open it.
• Reports are located in "C:\Users\Administrator\Documents\BabelfishCompass".

Review the Babelfish Compass Assessment Report

Let's review the report:

• We use the Babelfish version 2.2.0.
• The Assessment summary gives the analyzed objects, including supported and unsupported features.
• We see the SQL features are not supported in Babelfish.
• X-ref: 'Review Semantics' by SQL feature is here to review.

We can validate these reviews and then use the updated DDL file to populate the Babelfish database.

Create a Babelfish Cluster

This is done in the previous section under creating aurora cluster. We will use that database to update the DDL file.

Populate the Babelfish Database

We are using the SSMS query editor to Populate the Babelfish database. It involves the following steps:

• Open the SQL Server Management Studio.
• Go to the database page of AWS RDS and copy the writer instance endpoint name to connect to the server.
• Using the username, password, and writer instance endpoint to connect with the database.
• We are connected to the database.
• Enter the content of the updated DDL file and click on execute.

We populated the database onto the Babelfish database.