Follow dba-ninja.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

dba-ninja.com Links

Dba_db2_button

Sqlserver_dba_button

Babelfish Compass Tool - How to use

03 August,2022 by Rambler

The Babelfish Compass Tool is an assessment tool for compatibility between SQL Server & Babelfish.    

 

The following is an outline of the migration process with Babelfish:

1) Generate the DDL from the source database. More details below in the To get the DDL from the SQL Server section
2) Execute  the Babelfish Compass tool to assess  whether the application contains any SQL features not currently supported by Babelfish.
3) Review the Babelfish Compass assessment report and refactor or remove any unsupported SQL features. This step is probably the most complicated and could require multiple steps.

To progress with migrating to Babelfish you'll need to have an account and move through these stsps

4) Create the Babelfish cluster. Read more on Single-Mode Vs Multiple-Mode in Babelfish for Aurora PotgreSQL - which one should you use
5) Connect to the Babelfish database.  It's possible you've been utilising Active Directory for SQL Server connectivity , one of the review questions relates to whether you'll need Kerberos 
6) Execute the updated DDL against the Babelfish database.
7) Test and iterate until the migrated application’s functionality is correct.

 

To get the DDL from the SQL Server 

  1. Open SSMS and connect to your SQL Server instance as sa.
  2. Right-click the source database |  Tasks | Generate Scripts.
  3. Choose Next.
  4. Choose Select specific database objects and select all options  except Users.
  5. Choose Advanced. A new options window will appear
  6. Search and modify the following options

      Types of data to script – Choose Schema only
      Script Extended Properties – Choose False
      Script Owner – Choose True
      Script Object-Level-Permissions – Choose True
      Script Logins – Choose True
      Script Triggers  – Choose True
      Script Indexes – Choose True

      7. Save the output to a file . You'll be using this file to analyse through Babelfish Compass 

 

To generate the report 

Through the CommandLine navigate to the location of the Babelfish scripts . For this example the path is c:\BabelfishCompass

Execute the BabelfishCompass.bat  with the relevant parameters

C:\BabelfishCompass\BabelfishCompass.bat MyReportName C:/temp/mysqlDDL.sql

 

 

 

You'll notice in this report some text such as the following. This guides you to a different combination of the command line, which will : 

To generate this section, specify these options with -reportoption:
     'xref'  or  'xref=feature'





 Use the xref option on the -reportoption switch  , which will give you the added referal to the sql code 

BabelfishCompass.bat MyReportName -reportoption xref

 

Some general guidelines for reading the report 

1)The assessment summary shows the list of objects analyzed, including supported and unsupported features.

2) In the SQL Features section  of the report, you can  see the SQL features report starting from unsupported features.

You can review these using the cross-reference report which links to the exact location of each non-supported feature.

You can click on the line number, which takes you to the SQL for this unsupported feature, so you can review the details

3) Babelfish has some high-level migration advice through the -explain switch 

- Discuss the results of Babelfish Compass with the application owner and interpret the findings in the
context of the application to be migrated.
-  Keep in mind that a Babelfish migration involves more than just the server-side DDL/SQL code
(e.g. data migration, client applications, external interfaces, etc.)

 

More reading

Spotlight on Babelfish for Aurora PostgreSQL

Babelfish for Aurora PostgreSQL query cheatsheet

Download Babelfish Compass Tool 

64-bit Java/JRE not found. Please install 64-bit JRE 8 or later

If you get this error download and install the 64-bit JRE 8 . Confirm via   running the command  : java -version


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Babelfish Compass Tool - How to use


dba-ninja.com