Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog A Compatibility Evaluation Tool for Migrating SQL Server to PostgreSQL (Babelfish): Babelfish Compass

A Compatibility Evaluation Tool for Migrating SQL Server to PostgreSQL (Babelfish): Babelfish Compass

This article discusses the Babelfish Compass, including its background, evaluation guidelines, and tests.

By digoal

Background

The Babelfish Compass (short for COMPatibility ASSessment) tool analyzes SQL/DDL code for one or more Microsoft SQL Server databases to identify the SQL features incompatible with Babelfish for PostgreSQL.

SQL Server users can use Babelfish Compass to analyze the SQL/DDL code for their current SQL Server-based applications for compatibility with Babelfish. The purpose of such analysis is to inform a Go/No Go decision about whether it makes sense to consider starting a migration project from SQL Server to Babelfish. For this purpose, Babelfish Compass produces an assessment report which lists all SQL features found in the SQL/DDL code in great detail and whether or not these are supported by the latest version of Babelfish.

The Guidelines of Evaluation Tool babelfish_compass

Test Environment

  • PostgreSQL sql server Compatibility with babelfish-babelfish Deployment on mssql 2019 docker on ubuntu
docker pull mcr.microsoft.com/mssql/server:2019-latest
    
docker run -d -it --cap-add=SYS_PTRACE --privileged=true --name mssql2019 -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Mssql2o2o-" -e "MSSQL_PID=EnterpriseCore" -e "CONFIG_EDGE_BUILD=0" -p 1433:1433 mcr.microsoft.com/mssql/server:2019-latest
    
docker exec -it mssql2019 -u 0 -w /root /bin/bash

Download the Latest Compiled babelfish_compass

For example:

docker docker exec -it mssql2019 -u 0 -w /root /bin/bash
  
su - postgres
  
cd ~
wget https://github.com/babelfish-for-postgresql/babelfish_compass/releases/download/v.2022-12/BabelfishCompass_v.2022-12.zip
  
unzip BabelfishCompass_v.2022-12.zip
  
cd BabelfishCompass
chmod 500 BabelfishCompass.sh

If you download the Babelfish Compass source code, you need to package java to compass.jar.

root@109c95b407d8:~# apt-get install openjdk-11-jre openjdk-11-jdk

Test the babelfish_compass Evaluation Function

Create an SQL file that contains several table creation DDL statements for the SQL Server:

cd ~/BabelfishCompass
  
  
vi t.sql
create table a (id int, info text);
create table b (id int, info nvarchar(10));

View the instructions of using BabelfishCompass.sh commands:

postgres@109c95b407d8:~/BabelfishCompass$ ./BabelfishCompass.sh -help                     
Babelfish Compass v.2022-12, December 2022  
Compatibility assessment tool for Babelfish for PostgreSQL  
Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.  
  
Usage: BabelfishCompass.sh  <reportName>  [options]   
[options] can be:  
   inputfile [inputfile ...]    : one or more input files to import into the report  
   -delete                      : first deletes report directory, incl. all report files  
   -appname <appname>           : use application name <appname> for all inputfiles  
   -add                         : import additional inputfile(s) into existing report  
   -replace                     : replace already-imported input file(s)  
   -noreport                    : analyze only, do not generate a report  
   -importonly                  : import input file(s), no analysis or report  
   -reportonly                  : (re)generate report based on earlier analysis  
   -reportoption <options>      : additional reporting detail (try -help -reportoption)  
   -reportfile <name>           : specifies file name for report file (without .html)  
   -list                        : display imported files/applications for a report  
   -analyze                     : (re-)run analysis on imported files, and generate report  
   -userconfigfile <filename>   : specifies user-defined .cfg file (default= BabelfishCompassUser.cfg)  
   -nooverride                  : do not use overrides from user-defined .cfg file  
   -babelfish-version <version> : specify target Babelfish version (default=latest)  
   -encoding <encoding>         : input file encoding, e.g. '-encoding UTF16'. Default=US-ASCII  
                                  use '-encoding help' to list available encodings  
   -quotedid {on|off}           : set QUOTED_IDENTIFIER at start of script (default=ON)  
   -pgimport "<comma-list>"     : imports captured items into a PostgreSQL table for SQL querying  
                                  <comma-list> is: host,port,username,password,dbname  
                                  (requires psql to be installed)  
   -pgimportappend              : with -pgimport, appends to existing table (instead of drop/recreate)  
   -pgimporttable <table-name>  : table name for -pgimport; default=public.BBFCompass  
   -recursive                   : recursively add files if inputfile is a directory  
   -include <list>              : pattern of input file types to include (e.g.: .txt,.ddl)  
   -exclude <list>              : pattern of input file types to exclude (e.g.: .pptx)  
   -rewrite                     : rewrites selected unsupported SQL features  
   -noupdatechk                 : do not check for Babelfish Compass updates  
   -importfmt <fmt>             : process special-format captured query files  
   -nodedup                     : with -importfmt, do not de-duplicate captured queries  
   -noreportcomplexity          : do not include complexity scores in report  
   -syntax_issues               : also report selected Babelfish syntax errors (experimental)  
   -version                     : show version of this tool  
   -help [ <helpoption> ]       : show help information. <helpoption> can be one of:  
                                  reportoption, encoding, importfmt, exclude  
   -explain                     : some high-level migration guidance  
  
Babelfish Compass User Guide: https://github.com/babelfish-for-postgresql/babelfish_compass/blob/main/BabelfishCompass_UserGuide.pdf

Generate a compatibility evaluation report for t.sql:

postgres@109c95b407d8:~/BabelfishCompass$ ./BabelfishCompass.sh rep -replace ${PWD}/t.sql   
Babelfish Compass v.2022-12, December 2022  
Compatibility assessment tool for Babelfish for PostgreSQL  
Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.  
  
Reading BabelfishFeatures.cfg  
Latest Babelfish version supported: 2.3.0: BabelfishFeatures.cfg  
Reading /home/postgres/BabelfishCompassReports/BabelfishCompassUser.cfg  
  
Run starting               : 18-Jan-2023 09:03:39 (Linux)  
BabelfishFeatures.cfg file : v.2.3.0, Dec-2022  
Target Babelfish version   : v.2.3.0  
Command line arguments     : rep -replace  
Command line input files   : /home/postgres/BabelfishCompass/t.sql  
User .cfg file (overrides) : /home/postgres/BabelfishCompassReports/BabelfishCompassUser.cfg  
QUOTED_IDENTIFIER default  : ON  
Report name                : rep  
Report directory location  : /home/postgres/BabelfishCompassReports/rep  
Session log file           : /home/postgres/BabelfishCompassReports/rep/log/session-log-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html  
  
Importing /home/postgres/BabelfishCompass/t.sql, for application 't'  
Replacing input file /home/postgres/BabelfishCompass/t.sql  
Analyzing /home/postgres/BabelfishCompass/t.sql, for application 't'; #batches/lines: 1/2  
  
Generating report /home/postgres/BabelfishCompassReports/rep/report-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html.....................  
  
--- Run Metrics ----------------------------------------------------------------  
Run start            : 18-Jan-2023 09:03:39  
Run end              : 18-Jan-2023 09:03:40  
Run time             : 0 seconds  
#Lines of SQL        : 2  (2 lines/sec)  
SQL rewrite oppties  : 0  
Session log          : /home/postgres/BabelfishCompassReports/rep/log/session-log-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html  
Assessment report    : /home/postgres/BabelfishCompassReports/rep/report-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html  
================================================================================

Report file:

/home/postgres/BabelfishCompassReports/rep/report-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html

Export it outside the container macOS:

docker cp mssql2019:/home/postgres/BabelfishCompassReports/rep/report-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html /Users/digoal/Downloads/report-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html

Open it with a browser, and the contents of this report are listed below:

Report for: rep : Generated at 2023-Jan-18 09:03:39  
---------------------------------------------------  
  
Babelfish Compass version 2022-12, December 2022  
Compatibility assessment tool for Babelfish for PostgreSQL  
Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.  
  
Notice:  
This report contains an assessment based on the resources you scanned with the  
Babelfish Compass tool. The information contained in this report, including whether  
or not a feature is 'supported' or 'not supported', is made available 'as is',  
and may be incomplete, incorrect, and subject to interpretation.  
You should not base decisions on the information in this report without independently  
validating it against the actual SQL/DDL code on which this report is based.  
  
  
🕮  Babelfish Compass User Guide  
  
  
--- Report Setup ---------------------------------------------------------------  
BabelfishFeatures.cfg file : v.2.3.0, Dec-2022  
Target Babelfish version   : v.2.3.0  
Command line arguments     : rep -replace  
Command line input files   : /home/postgres/BabelfishCompass/t.sql  
User .cfg file (overrides) : /home/postgres/BabelfishCompassReports/BabelfishCompassUser.cfg  
Report name                : rep  
This report                : /home/postgres/BabelfishCompassReports/rep/report-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html  
Session log                : log/session-log-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html  
================================================================================  
  
--------------------------------------------------------------------------------  
--- Executive Summary for Babelfish v.2.3.0 ------------------------------------  
--------------------------------------------------------------------------------  
Total #lines of SQL/DDL: 2  
#Procedures/functions/triggers/views: 0    #Tables: 2  
  
  
  
  
--------------------------------------------------------------------------------  
--- Table Of Contents ----------------------------------------------------------  
--------------------------------------------------------------------------------  
    Executive Summary  
    Applications Analyzed  
    Assessment Summary  
    Object Count  
  
    Summary of SQL Features 'Not Supported'  
    Summary of SQL Features 'Review Manually'  
    Summary of SQL Features 'Review Semantics'  
    Summary of SQL Features 'Review Performance'  
    Summary of SQL Features 'Ignored'  
    Summary of SQL Features 'Supported'  
  
    X-ref: 'Not Supported' by SQL feature  
    X-ref: 'Review Manually' by SQL feature  
    X-ref: 'Review Semantics' by SQL feature  
    X-ref: 'Review Performance' by SQL feature  
    X-ref: 'Ignored' by SQL feature  
    X-ref: 'Supported' by SQL feature  
  
    X-ref: 'Not Supported' by object  
    X-ref: 'Review Manually' by object  
    X-ref: 'Review Semantics' by object  
    X-ref: 'Review Performance' by object  
    X-ref: 'Ignored' by object  
    X-ref: 'Supported' by object  
  
  
--------------------------------------------------------------------------------  
--- Applications Analyzed (1) --------------------------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
     t (2 lines SQL)  
  
--------------------------------------------------------------------------------  
--- Assessment Summary ---------------------------------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
    #applications            : 1  
    #input files             : 1  
    #SQL batches             : 1  
    #lines SQL/DDL processed : 2  
    #lines SQL in objects    : 0    (procedures/functions/triggers/views)  
    total #SQL features      : 6  
    Supported                : 6  
  
--------------------------------------------------------------------------------  
--- Object Count ---------------------------------------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
    TABLE : 2 (4 columns) without issues: 2 of 2  
  
=== SQL Features Report ========================================================  
  
--------------------------------------------------------------------------------  
--- SQL features 'Not Supported' in Babelfish v.2.3.0 --------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
-no items to report-  
  
--------------------------------------------------------------------------------  
--- SQL features 'Review Manually' in Babelfish v.2.3.0 ------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
-no items to report-  
  
--------------------------------------------------------------------------------  
--- SQL features 'Review Semantics' in Babelfish v.2.3.0 -----------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
-no items to report-  
  
--------------------------------------------------------------------------------  
--- SQL features 'Review Performance' in Babelfish v.2.3.0 ---------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
-no items to report-  
  
--------------------------------------------------------------------------------  
--- SQL features 'Ignored' in Babelfish v.2.3.0 --------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
-no items to report-  
  
--------------------------------------------------------------------------------  
--- SQL features 'Supported' in Babelfish v.2.3.0 --- (total=6) ----------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
DDL (2/1)  
    CREATE TABLE : 2  
Datatypes (4/3)  
    INT column : 2  
    NVARCHAR(10) column : 1  
    TEXT column : 1  
  
  
--------------------------------------------------------------------------------  
--- X-ref: 'Not Supported' by SQL feature --------------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
To generate this section, specify these options with -reportoption:  
     'xref'  or  'xref=feature'  
For more options and examples, use -help -reportoption  
  
--------------------------------------------------------------------------------  
--- X-ref: 'Review Manually' by SQL feature ------------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
To generate this section, specify these options with -reportoption:  
     'xref'  or  'xref=feature'  
For more options and examples, use -help -reportoption  
  
--------------------------------------------------------------------------------  
--- X-ref: 'Review Semantics' by SQL feature -----------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
To generate this section, specify these options with -reportoption:  
     'xref'  or  'xref=feature'  
For more options and examples, use -help -reportoption  
  
--------------------------------------------------------------------------------  
--- X-ref: 'Review Performance' by SQL feature ---------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
To generate this section, specify these options with -reportoption:  
     'xref'  or  'xref=feature'  
For more options and examples, use -help -reportoption  
  
--------------------------------------------------------------------------------  
--- X-ref: 'Ignored' by SQL feature --------------------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
To generate this section, specify these options with -reportoption:  
     'xref'  or  'xref=feature', and 'status=ignored' or 'status=all'  
For more options and examples, use -help -reportoption  
  
--------------------------------------------------------------------------------  
--- X-ref: 'Supported' by SQL feature ------------------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
To generate this section, specify these options with -reportoption:  
     'xref'  or  'xref=feature', and 'status=supported' or 'status=all'  
For more options and examples, use -help -reportoption  
  
--------------------------------------------------------------------------------  
--- X-ref: 'Not Supported' by object -------------------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
To generate this section, specify these options with -reportoption:  
     'xref'  or  'xref=object'  
For more options and examples, use -help -reportoption  
  
  
--------------------------------------------------------------------------------  
--- X-ref: 'Review Manually' by object -----------------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
To generate this section, specify these options with -reportoption:  
     'xref'  or  'xref=object'  
For more options and examples, use -help -reportoption  
  
  
--------------------------------------------------------------------------------  
--- X-ref: 'Review Semantics' by object ----------------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
To generate this section, specify these options with -reportoption:  
     'xref'  or  'xref=object'  
For more options and examples, use -help -reportoption  
  
  
--------------------------------------------------------------------------------  
--- X-ref: 'Review Performance' by object --------------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
To generate this section, specify these options with -reportoption:  
     'xref'  or  'xref=object'  
For more options and examples, use -help -reportoption  
  
  
--------------------------------------------------------------------------------  
--- X-ref: 'Ignored' by object -------------------------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
To generate this section, specify these options with -reportoption:  
     'xref'  or  'xref=object', and 'status=ignored' or 'status=all'  
For more options and examples, use -help -reportoption  
  
  
--------------------------------------------------------------------------------  
--- X-ref: 'Supported' by object -----------------------------------------------  
--------------------------------------------------------------------------------  
Back to Table of Contents  
  
  
To generate this section, specify these options with -reportoption:  
     'xref'  or  'xref=object', and 'status=supported' or 'status=all'  
For more options and examples, use -help -reportoption  
  
  
================================================================================  
  
--- Run Metrics ----------------------------------------------------------------  
Run start            : 18-Jan-2023 09:03:39  
Run end              : 18-Jan-2023 09:03:40  
Run time             : 0 seconds  
#Lines of SQL        : 2  (2 lines/sec)  
SQL rewrite oppties  : 0  
Session log          : /home/postgres/BabelfishCompassReports/rep/log/session-log-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html  
Assessment report    : /home/postgres/BabelfishCompassReports/rep/report-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html  
================================================================================

References

PostgreSQL Developers Migration SQL Server Cloud migration Tutorials Babelfish
0 1 0
Share on

Read previous post:

An Example of PostgreSQL join+order by limit Optimization

Read next post:

Configure MADlib for PolarDB to Realize the Database Machine Learning Function

digoal

276 posts | 24 followers

You may also like

Comments

digoal

276 posts | 24 followers

Related Products

More Posts by digoal

See All