This post gives step by step instructions on installing, running, and interpreting the output of AWS Babelfish Compass.
In case you're not already aware, Compass is a tool from AWS that allows you to evaluate the work that needs to be done to make a SQL Server schema compatible with their Babelfish PostgreSQL extension. There is a 30 page official user manual PDF available here. This article condenses that long user manual into actionable advice.
Compass is a downloadable software product, written in Java, that requires a few dependencies. We will start by discussing system requirements and installing those dependencies.
Compass does not require a particular operating system to run, but you will probably find it easier to run on a Windows host. The reason for this is that Compass requires a SQL schema dump file from SQL Server Management Studio (SSMS). SSMS is a Microsoft product that is only available on Windows.
Sorry Mac and Linux users :-/ ... I'm a Mac user myself, but sometimes it's just easier to use Windows. We'll assume you're on Windows from here on.
You will need to install Java and SSMS.
Compass specifically requires that you have the Java Runtime Environment (JRE) 8 or higher to run. You may already have it installed on your computer. To test, run the following command in CMD:
java –version
If it's installed, you will see output like this:
If it is not installed, head on over to the Java downloads page. Click "Windows Offline (64-bit)" and click through the installation prompts.
Once the installation is finished, re-open CMD and run "java -version" again. It should now be installed. If you get an error, try restarting your computer.
If you're getting your Java versions and paths messed up, try running this process from a fresh Windows host.
Just search for "SQL Server Management Studio Download" and click the first official Microsoft link that comes up.
Download the latest version of Compass at the download page on Github. You want the BabelfishCompass ZIP file, not the source code.
In the Windows File explorer, extract the downloaded ZIP to C:\BabelfishCompass. Here is what it looks like on my computer:
That's it! There is no need to install anything - just unzipping it in the right place is what you needed to do. You can confirm that installation was succesful by navigating to the folder called BabelfishCompass inside the extracted directory:
cd C:\BabelfishCompass\BabelfishCompass\
Then running:
BabelfishCompass.bat -help
Here is what it looks like on my computer:
Compass works by interpreting a special SQL file generated by SSMS. This SQL file contains definitions of your tables, stored procedures, constraints, indexes, and all other DB objects. Compass scans this file to create a report detailing any unsupported features and syntax.
Open up SSMS and follow the instructions on this page on the AWS docs, under the headline "Evaluating and handling differences between SQL Server and Babelfish." - the instructions are very straightforward. Save that file in a known place, such as your desktop or downloads folder. I prefer to save it as "<db_name>.sql" in the C:\BabelfishCompass\BabelfishCompass directory, so it is next to the binary file that will be scanning it.
At this point, you can run Compass on the file. If you saved it in the Babelfish directory, navigate to the directory:
cd C:\BabelfishCompass\BabelfishCompass
And execute the following command:
BabelfishCompass.bat <name_of_report> <name_of_sql_file_from_ssms>
Here is what it looks like on my computer:
Compass will automatically open your web browser and display the report.
Reading and interpreting this report is an artform in itself. By default, Compass raises a lot of red flags that are "false alarms" that can be safely ignored by using the correct Babelfish settings. In the future, we will be adding those details to this article. For now, check out the report, and contact us if you'd like help with any migration!
