SQL Server to PostgreSQL Migration with Babelfish

This article explores yet another option of SQL Server to PostgreSQL database migration using freeware tools. Babelfish is an open-source tool enabling database connections to PostgreSQL database from SQL Server clients. This means that applications originally developed for SQL Server can seamlessly work with PostgreSQL, requiring minimal code modifications compared to a traditional migration process and without the need to change database drivers.

By integrating Babelfish, PostgreSQL database cluster gains an additional endpoint that understands the SQL Server wire-level protocol and commonly used SQL Server statements. This approach allows client applications utilizing the Tabular Data Stream (TDS) wire protocol to establish native connections to the TDS listener port on PostgreSQL. Babelfish supports TDS versions 7.1 and higher, ensuring compatibility with a wide range of SQL Server client applications.

Using these capabilities, Babelfish can be involved into SQL Server to PostgreSQL database migration according to the following steps:

• Install Babelfish and ensure that a successful connection can be established using a client tool such as SSMS (SQL Server Management Studio) or sqlcmd. For Linux users, it is advisable to test the connectivity by utilizing sqlcmd from the Linux distribution of Microsoft SQL Server.
• To evaluate the migration to PostgreSQL/Babelfish, you can utilize the Babelfish Compass assessment tool. This tool is designed to assess complexity of the database and compatibility of T-SQL code with PostgreSQL/Babelfish. By running the tool, you can identify any T-SQL code that may require modification before running on the target system to ensure a smooth migration.
• Migrate SQL Server schema to the new PostgreSQL/Babelfish cluster. To do this, you need to generate the DDL (Data Definition Language) statements for all the database entries that have to be migrated from SQL Server to PostgreSQL. One way to accomplish this is by using SQL Server Management Studio (SSMS) to reverse-engineer the DDL. Open SSMS and navigate to the database, right-click on the database name and select the option to generate the script.
• Deploy schema on the PostgreSQL server by running the generated DDL through either sqlcmd or SQL Server Management Studio (SSMS). Babelfish implements most T-SQL statements, so porting schemas to PostgreSQL/Babelfish should be smooth and safe. In case of any issues, refer to differences in functionality between SQL Server and Babelfish and making necessary adjustments to the DDL code.
• After schema is successfully deployed on the PostgreSQL/Babelfish server, you can proceed with the data migration from SQL Server to PostgreSQL. There are various methods and tools available for data migration, such as using SSIS (SQL Server Integration Services), writing custom scripts, or utilizing third-party migration tools such as SQL Server to PostgreSQL converter.

At this point all client applications that work with SQL Server database should be updated to connect to the PostgreSQL/Babelfish instance instead of SQL Server. Modify the connection settings to point to the new data source and then make necessary testing. It is extremely important to run comprehensive tests covering various functionalities, queries, and scenarios within your applications. Pay close attention to any unexpected behavior, errors, or performance issues that may arise during SQL Server to PostgreSQL migration.

You have learned how to use Babelfish with SQL Server applications. Since the new data is actually stored in a PostgreSQL database, this is about SQL Server to PostgreSQL migration. Babelfish acts here as a proxy between SQL Server and PostgreSQL assisting with smooth and safe near-to-zero downtime migration between the two DBMS. The resulting database can be accessed with any PostgreSQL client connected to the appropriate PostgreSQL port (by default 5432) and not to the TDS port (by default 1433).

Have questions? Contact us