Blog: Increasing database performance by migrating from Ingres to SQL Server

One of our customers needed to migrate their Ingres Database system to Microsoft SQL Server wanting to improve the performance of their system. We came up with a migration solution and updated the business logic module of their application. As a result, the customer achieved a sufficient performance increase, improved the database system security, and got an optimized business logic architecture.

The video below depicts the migration process in details:

Customer’s issue

The customer’s original Ingres-based system comprised of three key elements. The Ingres database interacted with the customer’s Delphi application via business logic procedures. They written on the ABF language and located in the DLL libraries. Naturally, this system did not meet modern security standards and the customer’s performance requirements. Actually, the customer tried to migrate their system to Oracle a decade ago, but that challenge failed. This time they reached out to us needing to migrate their database to SQL Server and upgrade the business logic module to make it compatible with the new database and the old application client.

1

Our solution

On the first step we migrated the database from Ingres to SQL Server manually. Then we needed to connect the customer’s application to the newly created database system. Due to security reasons, we didn’t have access to the application’s source code. But still, we needed to upgrade the ABF business logic module. That would be just enough to set up the interaction of the customer’s Delphi application and the SQL Server database.

The ABF code in the customer’s business logic module contained both the procedural logic and SQL queries. That fact complicated the automated code conversion. So, we decided to split the conversion process into two parts. First, we extracted all SQL statements and converted them from Ingres SQL to T-SQL. On the next step, we converted the rest of the code from ABF to C++.

Automate it!

We’ve developed a special parser that divided the source code into two code branches. The parser extracted all SQL queries from ABF logic into the temporary file during the first pass. Throughout the second stage of code analysis, it parsed the Ingres SQL to determine the input and output parameters and their types. As a result, we obtained two files containing the data processing logic and SQL statements. The latter allowed us to convert the extracted SQL queries.

After that, we generated the C++ code from the procedural code using our automated ABF syntax converter. Finally, we compiled both files to DLL modules to make them compatible with the customer’s application.

As a result, we re-architectured the business logic module. Moreover, now the customer can use it to interact with any of the modern database platforms without globally changing the procedural logic.

3

Here’s how the new re-architectured system looks. The customer’s app calls procedures from the DLL Procedural module. This module calls an input or output parameter from the built-in module with SQL queries to access the Microsoft SQL Server database.

2

Customer’s benefits

Eventually, our customer got high-performance, efficient and scalable database system. Moreover, they obtained fully re-architectured and optimized business logic module according to the modern standards. In addition, our customer can now take full advantage of rich Microsoft SQL Server features to easily upgrade the database in nearest future.