Migration from SQL Server to PostgreSQL with 100% automation

Migration project is greatly facilitated with the help of Ispirer Toolkit. Due to 100% automation, the customer migrated 400 databases 10 times faster compared to the estimated time for manual migration.

Results

Background

A customer is a Dutch cloud-based ERP solution for real estate organizations. It is designed specifically for European real estate development, investment, and management sectors.

This platform allows real estate investors and managers to realize significant cost reductions for their portfolios, enhancing income and competitive edge through automation, AI, financial prediction, and market research. This ERP system includes technical assistance and management features for financial, lease, and maintenance tasks.

The users of the solution can add extensive portfolios to their real estate database, containing between 50 and 60,000 entries per client. The dashboard neatly organizes contacts, financial details, reports, property profiles, invoices, and billing information, all updated with real-time data. It enables users to perform cost calculations, message colleagues & clients, draft performance reports, create work orders, schedule viewings, manage maintenance, and conduct property audits.

Challenge

PostgreSQL is the undoubted leader gaining popularity among database administrators around the world. Companies migrate their enterprise databases to open source PostgreSQL for various reasons. But the most popular reason for switching to PostgreSQL is its rich functionality, a large number of opportunities for optimal interaction with data.

The client asked the Ispirer team to find the optimal way to migrate SQL Server databases to PostgreSQL. The task was to migrate 400 user databases with the same schema but different data. Normally, migrating a database takes several weeks to months, as several iterations are required to debug the new database.

To make things easier for users, the customer was looking for a solution that could migrate each of the 400 databases in the shortest possible time and with minimal risk of complications.

Customization of Ispirer Toolkit makes it possible to reach 100% automation for a specific migration, which makes it a perfect solution for our customer on this project.

Solution

In order to automate the migration of such a large number of databases, it was necessary to try Ispirer Toolkit at first and evaluate the migration quality. The client received the tool to test database migration with the necessary assistance from the Ispirer team. During the migration process, a number of difficulties were identified, and the solutions were subsequently implemented into the tool to improve the automatic migration of client databases. The main difficulties were as follows:

• conversion of the HierarchyId type and methods of working with it;
• conversion of STORED generated columns;
• STORED generated columns are not compatible with non-immutable functions;
• slow export from the source database.

The next stage of the project was to customize the tool based on the difficulties the client encountered when migrating with Ispirer Toolkit at the previous step.

First, the PostgreSQL ltree extension was initially considered as a solution for converting the HierarchyId type and the methods for working with it. However, this required converting the data into a different format. In addition, there were several differences between the source code and the PostgreSQL ltree functions that would have made conversion much more difficult. Therefore, the VARCHAR(892) type in PostgreSQL proved to be the best option, and functions were developed that were equivalent to the original ones, using only the “native” capabilities of PostgreSQL.

Second, the Ispirer experts added the conversion of STORED generated columns.

Third, to resolve the next issue our team developed additional immutable functions, since non-immutable couldn’t be used.

Fourth, to create an ODBC connection, it was recommended to use another driver (sqlncli11.dll), which helped speed up downloading and conversion more than 20 times.

As a result of setting up the tool, the client purchased an Ispirer Toolkit license and successfully migrated all SQL Server databases to PostgreSQL on their own. Our team accompanied the client throughout the entire migration journey and helped with all the operational questions they had.

Outcome

As a result of the project, Ispirer Toolkit has been improved to address a number of PostgreSQL conversion issues, which will help avoid similar conversion issues in future projects. The outcomes the client received was:

• 100% automation of migration from SQL Server to PostgreSQL after customizing the tool
• Automation and a large size of a migration project made it possible to customize the tool for successful migration of all the databases with a 10 times faster process compared to manual conversion.