Background
An increasing number of organizations are planning to move away from proprietary databases to open source for various reasons such asavoiding expensive costs and licensing terms while leveraging the flexibility and database freedom of modern offerings.
Migrating from legacy and commercial databases is often a challenging, time-consuming and resource-intensive undertaking. In a legacy / enterprise environment, the database is often used by a variety of desktop and web applications, sometimes using different programming languages. Migrating the database would then require much work on those applications to rewrite the layer that interacts with the database. Many organizations are not willing to make such investment on these legacy applications.
Babelfish for Aurora PostgreSQL is a new capability that makes it possible to run Microsoft SQL Server applications directly on Amazon Aurora with little to no code changes.
In this tutorial, we look at how we can perform the migration on the infamous Northwind database and some of the challenges and considerations while migrating much larger and complex databases.
I used a sample Northwind .NET application available on Github for testing purposes to illustrate the different kinds of operations that we could run against the database. I also used the database script from Microsoft.
Compatibility Assessment
Before starting the migration we need to assess the compatibility of our Microsoft SQL database with Babelfish for PostgreSQL. Such assessment will help us determine the feasibility and complexity of the migration.
Babelfish Compass is a tool that analyzes the SQL/DDL code for the database and determines which features are/aren't supported so you would know ahead of time how to make the migration successful and what sort of tweaks you might need to do on your existing database.
To start with the assessment, I need to generate the SQL script for the database, something I can achieve using the SQL Server Management Studio if I don't have it already. Since I downloaded the script, I skipped that.
I also need to have Babelfish Compass installed.
At this point I ran the assessment command.
sh ./BabelfishCompass.sh MyReport ~/Documents/Temp/NorthwindCore/src/scripts/instnwnd.sql -appname NorthwindCore
The command ran successfully and the result looked promising.
The report was generated and I can analyze the results using the HTML page.
Luckily for me, there were no issues highlighted in the report... I may have chosen the database on purpose.
In a real world scenario, you would need to make a decision on the migration following the report. You may be required to do further work on your database or tweak certain procedures or functions.
Limitations and Considerations
Achieving full compatibility with Babelfish for PostgreSQL for large and complex databases is difficult. You can use this guide to learn more about the limitations and the features that are not supported and determine if it's the right tool for your database.
Another consideration is the version of PostgreSQL. At the time of writing, Babelfish supports only PostgreSQL version 13.4 and newer, which is not yet available for the serverless capacity type.
As for region availability, Babelfish for Amazon Aurora PostgreSQL is available in all regions supported by Aurora PostgreSQL.
On the application side, a discovery of the tools and frameworks is essential to assess the feasibility and risks ahead of the migration. Using Entity Framework or similar ORMs may or may not work, based on the setup, dependencies, versions and other factors.
Preparations
I created a new Microsoft SQL Server RDS instance to act as the source database.
I connected to the database using DataGrip (you can also use SSMS) and ran the northwind database script that I downloaded earlier.
I then created the target Aurora PostgreSQL database cluster with a single writer instance. Note the database version that I used which is the minimum required to support Babelfish. You can also use the filters to show only the supported versions.
I also turned Babelfish on in the settings.
You can read more about the different migration modes (single database vs multiple databases) in the documentation.
I ended up with a source and a target database.
Schema Conversion
In a standard database migration project, you can use the AWS Schema Conversion tool (AWS SCT) to convert your existing database schema from one database engine (i.e. MS SQL Server) to another (i.e. PostgreSQL).
If you attempt to do so with the babelfish-enabled PostgreSQL database you will get all sorts of errors and permission issues because Babelfish maintains the database objects information in its sys database.
For this reason, I connected to the PostgreSQL instance using SQL Server Management Studio (SSMS) to create the northwind database. Instead of using the default SSMS Object Explorer, I used the New Query window to connect.
I then ran the following script:
CREATE DATABASE northwind
Finally, I extracted the schema creation script from the Northwind script file I downloaded and I ran it in the same query window in SSMS.
You could run into multiple iterations of code adjustments here.
Upon completion, I was able to browse the created babelfish_db in DataGrip by connecting to the PostgreSQL instance using the PostgreSQL driver.
I could have ran the entire script at this point to add the data, but I wanted to follow the steps of a real-world migration project and opt to use a full load using the Database Migration Service.
Database Migration
The schema has been created in the PostgreSQL database and I was ready to start migrating the data.
Using the AWS Database Migration Service (DMS), I created a new replication instance first.
I also created two endpoints representing our source and target databases.
At this point, I created the database migration task and ran a full load of the data into the target PostgreSQL database. For the transformations and mappings, I selected the "convert-lowercase" rule for the tables and columns in the dbo schema.
The data has been loaded successfully and I was ready to run the app.
Testing the App
Before I declared success, I wanted to run the application by pointing to the Microsoft SQL Server database and then switching the connection string to point to the new PostgreSQL instance.
After a few rounds of minor code changes, mainly because of the Entity framework setup in the sample application, my test was successful!
I recorded a few steps of the testing I did.
Conclusion and Considerations
If you are planning a migration from MS SQL Server to open source then Babelfish is a fantastic tool. It provides the capability for PostgreSQL to understand queries from applications written for Microsoft SQL Server. It can also provide you a quick migration path, with no or minor changes to your application.
Before using Babelfish and deciding whether it is the right one for you, I encourage you to check the documentation and understand how the capability works and what limitations are relevant to the applications you are trying to migrate.
Top comments (0)