Migrate SQL Server to Babelfish for Aurora PostgreSQL using the Compass tool and AWS DMS

Many AWS customers are starting to modernize architectures and move toward open-source databases. Babelfish for Aurora PostgreSQL makes it easier to migrate applications from SQL Server to Amazon Aurora PostgreSQL-Compatible Edition. With Babelfish, Aurora PostgreSQL-Compatible supports commonly used T-SQL language and semantics, which reduces the amount of code changes related to database calls in an application.

In this post, we show how to migrate a Microsoft SQL Server database to Babelfish, including data migration using AWS Database Migration Service (AWS DMS) and will use SQL Server Northwind sample database for the migration. Additionally, we look into some common issues and their potential resolution.

Babelfish overview

Before we discuss the step-by-step migration process, let’s look at the Babelfish architecture. Babelfish is a migration accelerator for moving SQL Server applications to Aurora PostgreSQL-Compatible with minimal changes to your T-SQL code or client connectivity drivers. It allows you to move from a licensed proprietary on-premises database management system (DBMS) to an open-source DBMS in the cloud and enables the path for modernization.

Babelfish provides the capability for PostgreSQL to understand queries from applications written for SQL Server. Babelfish understands the SQL Server Wire Protocol and T-SQL, the SQL Server query language, so you don’t have to switch database drivers or rewrite all of your application queries. You can create TDS connections using the SQL Server connectivity libraries and also standard PostgreSQL connections, as shown in the following diagram.

The following are some benefits of Babelfish:

• Keep existing queries – Language extensions enable Aurora PostgreSQL-Compatible to work with T-SQL
• Accelerate migration – You can complete migration faster, saving months to years of work
• Freedom to innovate – You can run T-SQL code side by side with open-source functionality and continue to develop with familiar tools

Solution overview

The following are the high-level steps for Babelfish migration:

1. Generate DDL and perform analysis using the Babelfish Compass tool. We can generate DDL using the following options:
   1. Use SQL Server Management Studio (SSMS) to export the database schema.
   2. As of Babelfish Compass v.2023-08, Compass may optionally perform the DDL generation directly for the SQL Server databases for which analysis needs to be performed.
2. Create an Aurora PostgreSQL instance with the Babelfish extension.
3. Recreate the schema in Babelfish by running the DDL script against port 1433.
4. Migrate data from SQL Server to Babelfish using AWS DMS and reconfigure the client application to connect to the Babelfish TDS port instead of SQL Server.

For .NET applications with a backend as Babelfish, little to no changes are required in application code. Changes are required in the application code for the features not supported by Babelfish.

In the following sections, we provide the step-by-step details to migrate from SQL Server to Babelfish for Aurora PostgreSQL.

Babelfish Compass assessment tool

Babelfish Compass is a standalone tool that runs on Windows, Mac, and Linux, and assesses compatibility for Babelfish. This tool looks at the DDL and SQL code and provides a detailed report listing all supported and unsupported features.

To install and run Babelfish Compass, complete the following steps:

1. Download the latest version of Babelfish Compass (.zip file) from GitHub.
   
2. Unzip the file to the required folder. There are three zip files in the folder. You should unzip the file BabelfishCompass_v.2023-08.zip.
   
3. Babelfish Compass requires 64-bit Java/JRE 8 or later; it must be installed prior to running the Compass report.
4. Open CMD (Windows) and navigate to the path where you unzipped the zip file.
5. To verify installation, run BabelfishCompass with -help option.

   BabelfishCompass -help

Generate Compass report for Northwind database

Use the Compass tool capability to generate DDLs with Compass v.2023-08 or later. This option is useful when there are many SQL Server instances or many databases in a SQL Server. To let Compass, perform the DDL generation, the following command-line options must be specified. Compass will then connect to the SQL Server, generate the DDL files, and run a Compass analysis on the generated files.

• –sqlendpoint – The hostname or IP address of the SQL Server. Optionally, the port number can also be specified (for example, 10.123.45.67,1433 or mybigbox,1433).
• –sqllogin – The login name to connect to the SQL Server. This login should typically be a member of the sysadmin role so as to have permission to generate the DDL.
• –sqlpasswd – The corresponding password.
• –sqldblist – This is optional; when omitted or when all is specified, DDL is generated for all user databases in the server. Alternatively, a comma-separated list of database names can be specified.

The speed of DDL generation highly depends on the network proximity to the SQL Server. If it takes too long, you can cancel the process using CTRL+C and do manual DDL generation through SSMS, which may be faster.

BabelfishCompass Northwind-report5 -sqlendpoint <endpoint> -sqllogin <login> -sqlpasswd
<password> -sqldblist Northwind -optimistic -reportoptions xref -rewrite

Above script generates, Babelfish compass report and by default it goes in your user’s Documents\\BabelfishCompass folder. The script also generates separate DDL files for each database you specify and stores it in your computers TEMP directory as shown in below screen shot.

Other option to generate compass report is to generate DDL using SSMS first and run Babelfish compass commands to generate report, refer to Deep dive into Babelfish Compass to learn more details.

Analyze the results

To assess the overall migration efforts, the first thing to do is to look at the summary section at the top of the report, where the number of objects and the number of lines of SQL code are reported. This will give a first impression of how big is application and much effort it may take.

--- Report Setup ---------------------------------------------------------------
BabelfishFeatures.cfg file : v.3.2.0, Jun-2023
Target Babelfish version   : v.3.2.0 (PG 15.3)
Command line arguments     : Northwind-report SMOTest -sqlendpoint ****** -sqllogin **** -sqlpasswd ******** -sqldblist Northwind
DDL input files            : Northwind_SMO_DDL_2023-Aug-10.sql
DDL input files location   : C:\Users\ADMINI~1\AppData\Local\Temp\2\CompassAutoDDL-2023-Aug-10-01.36.40
User .cfg file (overrides) : C:\Users\Administrator\Documents\BabelfishCompass\BabelfishCompassUser.cfg
Report name                : Northwind-report
This report                : C:\Users\Administrator\Documents\BabelfishCompass\Northwind-report\report-Northwind-report-bbf.3.2.0-2023-Aug-10-01.37.18.html
Session log                : log\session-log-Northwind-report-bbf.3.2.0-2023-Aug-10-01.36.40.html
================================================================================

--------------------------------------------------------------------------------
--- Executive Summary for Babelfish v.3.2.0 ------------------------------------
--------------------------------------------------------------------------------
Total #lines of SQL/DDL: 1272
#Procedures/functions/triggers/views: 23    #Tables: 13

SQL features not supported by Babelfish       : 127
Estimated complexity of not-supported features: medium:127

--------------------------------------------------------------------------------

 --------------------------------------------------------------------------------
--- Applications Analyzed (1) --------------------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents

     Northwind (1272 lines SQL)

--------------------------------------------------------------------------------
--- Assessment Summary ---------------------------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents

    #applications            :    1
    #input files             :    1
    #SQL batches             :  350
    #lines SQL/DDL processed : 1272
    #lines SQL in objects    :  208    (procedures/functions/triggers/views)
    total #SQL features      : 1208
    Supported                :  750
    Not Supported            :    0    
    Review Semantics         :   45
    Ignored                  :  413

Overridden by user .cfg file (C:\Users\Administrator\Documents\BabelfishCompass\BabelfishCompassUser.Optimistic.cfg):
    Ignored (was: Not Supported) : 129

--------------------------------------------------------------------------------
--- Object Count ---------------------------------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents

    DATABASE                            :  1                                                                                 
    LOGIN                               : 51                                                                                 
    PROCEDURE                           :  7 (72 lines SQL)  without issues: 7 of 7: list
    TABLE                               : 13 (144 columns)   without issues: 13 of 13: list  
    VIEW                                : 16 (136 lines SQL) without issues: 16 of 16: list   
    constraint CHECK                    :  8                                                                                 
    constraint FOREIGN KEY              : 13                                                                                 
    constraint PRIMARY KEY              : 13                                                                                 
    index                               : 26                                                                                 
    user-defined datatype (UDD), scalar :  3          

Next, focus on the following Summary of SQL Features sections: Not Supported, Review Manually, Review Semantics, Review Performance, and Ignored.

Based on the issues, either develop the possible workaround or descope the migration by scaling down the features not required in migrated application. The descope process requires collaboration between developers and the application owner. When we use Optimistic option in the command, then some of the commands will list under Ignored rather than not supported. If we generate the report without Optimistic clause, below listed features will be reported under not supported. The features ignored with the Optimize option can generally be ignored for purposes of getting the application migrated. Please note that when we generate compass report for Northwind schema using the optimistic setting, there are no unsupported features.

 --------------------------------------------------------------------------------
--- SQL features 'Ignored' in Babelfish v.3.2.0 --- (total=413) ----------------
--------------------------------------------------------------------------------
Back to Table of Contents

DDL (286/14)
    Option ALLOW_PAGE_LOCKS=ON, constraint PRIMARY KEY, in CREATE TABLE : 13
    Option ALLOW_PAGE_LOCKS=ON, Index, in CREATE INDEX : 26
    Option ALLOW_ROW_LOCKS=ON, constraint PRIMARY KEY, in CREATE TABLE : 13
    Option ALLOW_ROW_LOCKS=ON, Index, in CREATE INDEX : 26
    Option DROP_EXISTING=OFF, Index, in CREATE INDEX : 26
    Option IGNORE_DUP_KEY=OFF, constraint PRIMARY KEY, in CREATE TABLE : 13
    Option ONLINE=OFF, Index, in CREATE INDEX : 26
    Option OPTIMIZE_FOR_SEQUENTIAL_KEY=OFF, constraint PRIMARY KEY, in CREATE TABLE : 13
    Option OPTIMIZE_FOR_SEQUENTIAL_KEY=OFF, Index, in CREATE INDEX : 26
    Option PAD_INDEX=OFF, constraint PRIMARY KEY, in CREATE TABLE : 13
    Option PAD_INDEX=OFF, Index, in CREATE INDEX : 26
    Option SORT_IN_TEMPDB=OFF, Index, in CREATE INDEX : 26
    Option STATISTICS_NORECOMPUTE=OFF, constraint PRIMARY KEY, in CREATE TABLE : 13
    Option STATISTICS_NORECOMPUTE=OFF, Index, in CREATE INDEX : 26
Permissions (39/2)
    ALTER AUTHORIZATION ON object TO SCHEMA OWNER : 36
    ALTER AUTHORIZATION ON TYPE TO SCHEMA OWNER : 3
Users (88/4)
    ALTER SERVER ROLE processadmin ADD MEMBER : 1
    ALTER SERVER ROLE setupadmin ADD MEMBER : 1
    Login option CHECK_EXPIRATION, in CREATE LOGIN : 43
    Login option CHECK_POLICY, in CREATE LOGIN : 43

Create a Babelfish for Aurora PostgreSQL cluster

Creating a Babelfish cluster is similar to creating an Aurora PostgreSQL cluster, except we expand the filter options under Engine version and select Show versions that support the Babelfish for PostgreSQL feature. It is recommended to choose the latest major release version of Aurora PostgreSQL which has the most recent features and performance improvements.

When you create a Babelfish cluster, you choose between using a single migrated T-SQL user database or multiple migrated T-SQL user databases together. If you specify single-db, you can create only a single T-SQL database in Babelfish, and T-SQL schemas are created as regular PostgreSQL schemas in your Babelfish database. In multiple-database mode, the schema names of user databases become dbname_schemaname when accessed from PostgreSQL. The schema names remain the same when accessed from T-SQL. See Using Babelfish with a single database or multiple databases for more information.

We recommend using multi-db for the migration mode since this provides support for multiple T-SQL user databases in your Babelfish cluster. You cannot change this value after you create the cluster, so it’s better to have the flexibility even if you initially need only one T-SQL user database.

To stay closer to the SQL Server, you can choose sa as the primary user name.

After you create the Babelfish cluster, it will provide two endpoints, a reader and writer, and it will also support both PostgreSQL and T-SQL on different ports.

Connect to Babelfish Cluster

The next step is to make the connection with the Babelfish cluster and create a schema in the new database using SQL Server Management Studio (SSMS). SSMS is a GUI-based client tool to connect to SQL Server with limited Object Explorer support for viewing and scripting objects.

Enter your Babelfish endpoint cluster and credentials, then choose Connect.

Recreate the schema in Babelfish by running the DDL script in SSMS

To recreate schema in Babelfish, follow below steps:

1. Check Babelfish as well as Aurora PostgreSQL versions using the following query:

   -- Check your version
   SELECT SERVERPROPERTY('babelfishversion') AS BabelfishVersion
   	, aurora_version() AS AuroraPostgreSQLVersion
   	, @@VERSION AS ClassicSQLServerVersion
   GO
   

   

2. Configure Babelfish escape hatches to ignore – Babelfish mimics SQL behavior for control flow and transaction state whenever possible. When Babelfish encounters an error, it returns an error code similar to the SQL Server error code. To handle statements that might fail, Babelfish defines certain options called escape hatches. An escape hatch is an option that specifies Babelfish behavior when it encounters an unsupported feature or syntax. Use below statement to set all escape hatches to ignore strict behavior:

   EXECUTE sp_babelfish_configure 	'%', 'ignore', 'server';
   GO
   

3. Create Northwind database and verify using T-SQL catalog view:

   -- T-SQL catalog views to display databases
   SELECT * FROM sys.databases;
   GO
   

   -- T-SQL catalog views to display databases
   SELECT * FROM sys.databases;
   GO
   

   

4. Check schema mapping between Babelfish and PostgreSQL, this information will be useful for creating DMS task for data loading.

   SELECT 
   	pg.dbname AS BabelfishDBName
       , be.orig_name AS SchemaName  
   	, pg.nspname AS PGSchemaNameForDMS
   	, pg.oid
   	, SCHEMA_ID( be.orig_name ) AS MapsToPGOID
   FROM sys.pg_namespace_ext AS pg
   INNER JOIN  sys.babelfish_namespace_ext AS be 
   	ON pg.nspname = be.nspname
   WHERE dbname = DB_NAME()
   ORDER BY SchemaName;
   

   Depending on multi-db or single-db configuration selected for your Babelfish cluster, you will see different results because PostgreSQL internally maps database and schema names differently for each of these options.

   Results of multi-db Babelfish cluster:

   

   Results of single-db Babelfish cluster:

   

5. Create schema objects in Babelfish by running the finalized scripts.
   1. Babelfish currently supports migrating BINARY, VARBINARY, and IMAGE data types using the BYTEA data type with DMS. Make changes in Northwind database script to change any IMAGE datatype to BYTEA. In the original Northwind database the Category table Picture column uses the IMAGE data type and Employee table Photo column uses the IMAGE data type. In the final script, find and replace as shown below:

      [image] 
      with [bytea] /* [image] */

   2. Run scripts with only primary key and unique keys on the tables, this will speed up the data loading process and we can create additional indexes and constraints once data load process finishes.
   3. Validate the tables created in previous step using below scripts:

      --using T-SQL view 
      SELECT * FROM sys.tables;

Migrate data from SQL Server to Babelfish using AWS DMS

AWS DMS version 3.5.1 provides better support data type support for Aurora PostgreSQL with Babelfish minor versions 14.8 and 15.3 or later. Here is how to get started with using DMS with the SQL Server source and Aurora PostgreSQL target endpoints.

1. On the AWS DMS console, create a replication instance by choosing the size of the instance depending upon your workload.
2. Create the source and target endpoints.
   1. Source endpoint – The Source endpoint should point to the SQL Server.
   2. Target endpoint – The Aurora PostgreSQL target endpoint is the preferred way to migrate data to Babelfish. When you create your AWS DMS target endpoint using the AWS DMS console, API, or CLI commands, keep following things in mind:
      • Specify the target engine as Amazon Aurora PostgreSQL
      • Name the database, babelfish_db.
      • In the Endpoint Settings section, add settings to set DatabaseMode to Babelfish and BabelfishDatabaseName to the name of the target Babelfish T-SQL database. Please note that If you are using Aurora PostgreSQL with Babelfish prior to minor versions 15.3 and 14.8, do not use these endpoint settings.
3. Create and start the replication task to load the required tables in the target database. For more information about Babelfish as an AWS DMS endpoint, refer to Using Babelfish as a target for AWS Database Migration Service.
   For migrating Northwind database, below are task settings:
   • Target table preparation – “Do Nothing”. In this mode AWS DMS will not make any changes in the target database. you should select Full LOB mode to take advantage of the new capability with DMS 3.5.1 with Babelfish 3.2.0 and 2.5.0 releases.
   • Table Mapping Rule – Add following rule to include all tables in the dbo schema where schema name is like ‘dbo’ and Source table name is like ‘%’ or specific table from your source SQL Server database.
     

   Transformation rules – The following are the transformation rules needed depending on multi-db or single-db configurations:

   • If you use multi-db mode rename the dbo schema to match your PostgreSQL schema which is northwind_dbo
   • For both the modes, rename the tables to lowercase. Below screen shot displays the transformation rule:
4. Review the table statistics to confirm data load. Once the DMS replication task completes successfully, we can view the table statistics.
   

Alternatively, we can run query the database to validate the data.

SELECT TOP 10 * FROM categories ORDER BY [CategoryID]

Change the BYTEA data types back to IMAGE – Most of the applications works fine with BYTEA but if we need to change it back to IMAGE, use below script to add new column with IMAGE data-type, then copy data and drop original column and finally rename to match original column name.

ALTER TABLE [dbo].[Categories] ADD [PictureImage] IMAGE NULL;
UPDATE [dbo].[Categories]
SET [PictureImage] = [Picture];

SELECT
[CategoryID]
, CASE WHEN CAST( [Picture] AS Image ) = [PictureImage] THEN 'Match' ELSE 'No Match'
END AS [Compare]
FROM [dbo].[Categories];

ALTER TABLE [dbo].[Categories] DROP COLUMN [Picture];
-- Rename column

EXEC sp_rename 'categories.pictureimage', 'Picture', 'COLUMN';

Reset your IDENTITY columns after loading data from DMS. In SQL Server, it is very common to use IDENTITY columns for the primary key or other columns which needs auto incremented values. After migrating data with tables that use IDENTITY columns or the SERIAL data type, reset the PostgreSQL-based sequence object based on the maximum value for the column using below script:

-- following T-SQL query to generate statements to seed the associated sequence object.

USE Northwind;
GO

DECLARE @schema_prefix NVARCHAR(200) = ''

IF current_setting('babelfishpg_tsql.migration_mode') = 'multi-db'
        SET @schema_prefix = db_name() + '_'

SELECT 'SELECT setval(pg_get_serial_sequence(''' + @schema_prefix + schema_name(tables.schema_id) + '.' + tables.name + ''', ''' + columns.name + ''')
               ,(select max(' + columns.name + ') from ' + schema_name(tables.schema_id) + '.' + tables.name + '));'
FROM sys.tables tables
JOIN sys.columns columns ON tables.object_id = columns.object_id
WHERE columns.is_identity = 1

UNION ALL

SELECT 'SELECT setval(pg_get_serial_sequence(''' + @schema_prefix + table_schema + '.' + table_name + ''', 
''' + column_name + '''),(select max(' + column_name + ') from ' + table_schema + '.' + table_name + '));'
FROM information_schema.columns
WHERE column_default LIKE 'nextval(%';

The follwoing statements are generated by the previous queries. Run them against Northwind database:

-- Generated statements
SELECT setval(pg_get_serial_sequence('northwind_dbo.categories', 'categoryid')                 ,(select max(categoryid) from dbo.categories));
SELECT setval(pg_get_serial_sequence('northwind_dbo.employees', 'employeeid')                 ,(select max(employeeid) from dbo.employees));
SELECT setval(pg_get_serial_sequence('northwind_dbo.orders', 'orderid')                 ,(select max(orderid) from dbo.orders));
SELECT setval(pg_get_serial_sequence('northwind_dbo.products', 'productid')                 ,(select max(productid) from dbo.products));
SELECT setval(pg_get_serial_sequence('northwind_dbo.shippers', 'shipperid')                 ,(select max(shipperid) from dbo.shippers));
SELECT setval(pg_get_serial_sequence('northwind_dbo.suppliers', 'supplierid')                 ,(select max(supplierid) from dbo.suppliers));

Using the Generate Script wizard in SSMS, you will regenerate the create scripts as follows:

1. Script just the database tables, to include check constrains, indexes, triggers, etc. in separate file. Comment out the table creation section as we already migrated the tables. Final script should only contain indexes, constrains and triggers. Run the script against Northwind database in Babelfish cluster.
2. Script just the views and run it against Northwind database in Babelfish cluster.
3. Script just the stored procedures, fix any non-supported features and run it against Northwind database in Babelfish cluster.

Migration issues and potential resolution

In this section, we discuss some of the issues and potential resolutions for Babelfish as version 3.2

that we have seen with customers in previous migration efforts.

Unsupported features with stored procedures and functions

In order to start testing your application faster, one technique is to comment out the unsupported stored procedure bodies and throw an exception/raise an error. We will still maintain the stored procedure signature (name, input & output). Theoretically, you can write a parameterized regular expression (with group capture) to do this. Below is an example:

This way, you can isolate unsupported issues and tackle them separately release to get these fixed rather than holding up all testing efforts due to subset of unsupported features which may take some time to resolve.

Troubleshooting queries

This is the most common issue after migration to Babelfish. Many of these issues are often attributed to different index strategies between SQL Server and PostgreSQL that can be diagnosed with EXPLAIN ANALYZE on the PostgreSQL side or using SET BABELFISH_STATISTICS PROFILE.

A common issue is that SQL Server can still use an indexed column when the predicate has an expression on the column, but PostgreSQL may not. The solution is to create an expression index in PostgreSQL matching the predicate expression. Once you analyze the queries, apply the potential fixes by ensuring appropriate indexes and join conditions. Use the following code:

• Use SET BABELFISH_STATISTICS PROFILE {ON|OFF} to display the query plan used to run a statement
• Use SET BABELFISH_SHOWPLAN_ALL {ON|OFF} to display estimated explain plans for a statement without performing the command

In the following example, BABELFISH_SHOWPLAN_ALL provides details of the access path, index scan (if any), and cost of the operation, which will help in performance tuning:

SET BABELFISH_STATISTICS PROFILE ON 
GO

SELECT * FROM test  a JOIN test2 b ON a.a = b.a WHERE b.c = 'A926'

In this example, to improve performance, we create an index on test2 on column c; this is reflected in the explain plan and the cost of the operation will also reduce:

CREATE INDEX ix_test_1 ON test2 (c);

For more information, refer to Reviewing a Query Plan.

Update statistics

Statistics of database objects is critical for the performance of the queries. Database optimizers use these statistics to determine the best execution plan and use it for data retrieval.

Gathering and updating stats is not supported in Babelfish as of this writing. As a workaround solution, use PostgreSQL features to analyze the table:

• SQL Server command – UPDATE STATISTICS
• PostgreSQL command – ANALYZE

For more information, refer to ANALYZE.

Rebuild table indexes

Indexes are important for query performance. In T-SQL, we use either the DBCC command or ALTER INDEX command for the re-indexing:

DBCC DBREINDEX ('northwind.dbo.employees’);

ALTER INDEX ALL ON northwind.dbo.employees REBUILD;

These commands are not supported in Babelfish; instead, we can use the PostgreSQL statement REINDEX TABLE from the PostgreSQL connection.

REINDEX TABLE dbo.employees’;

For more details, refer to Rebuilding Table Indexes.

Unsupported code features in Babelfish

There are some differences in Babelfish functions or code syntax and SQL Server. The following table provides a few examples of workarounds.

Summary

In this post, we showed the steps involved in migrating a SQL Server application to Babelfish for Aurora PostgreSQL, including using the Babelfish Compass tool and AWS DMS. We discussed which sections to focus on in the Babelfish Compass report to get more details about unsupported features. We also showed details about loading data into the Babelfish cluster using AWS DMS. Lastly, we presented common migration issues and potential resolutions that can help accelerate Babelfish migrations.

The AWS Babelfish team is making continuous improvements to the product and adding new features on a regular basis. Check out the Babelfish for Aurora PostgreSQL updates with each quarterly release for the latest improvements

For more information about Babelfish for Aurora PostgreSQL, refer to Babelfish for Aurora PostgreSQL.

About the Author

Amit Arora is a Solutions Architect with a focus on database and analytics at AWS. He works with our financial technology, global energy customers and AWS certified partners to provide technical assistance and design customer solutions on cloud migration projects, helping customers migrate and modernize their existing databases to the AWS Cloud.