Roman Viktorovich — How we solved the problem of data lost in the course of transfer to the new Firebird-based information system

Historically, our organization used MS SQL Server as a DBMS. Over 10 years, we developed and maintained the information system based on MS Access replacing it with new developments.

In 2010, our company had its 15-year anniversary. The top management decided to purchase a ready-made and polished by years information system that will cover most business areas. The information system chosen was Vostochnyi Express.

We started its implementation and the first questions appeared. A situation typical for a new system presentation occurred:

• Providers of the information systems were asked: «Is it possible to inherit all the data from our old information systems?»
• To mix in, they answered: «Yes, of course. We have developed a number of formats to upload data from other systems, so your data will be uploaded».

The upload took place. Oh, God! Some imported data was distorted or lost. How were we to find the difference, taking into account that the source data was on MS SQL Server and the target DBMS was Firebird. The providers of Vostochny Express shrug their shoulders: «Probably errors occurred when the data was exported from the old system». I.e. they blamed us of inappropriate preparation of transport files.

Ok! Let’s try to create a linked server on MS SQL Server and compare the data. Oops! A bad luck! We don’t have the OLE DB Provider for Firebird or InterBase. Let’s search for it in the Internet and ask everybody. So, we had the following options:

• use Firebird ODBC Driver
• use IBProvider

Other options were also available, we tried them and excluded after a preliminary study. The first two options passed the test: the linked server could be created and the data could be extracted. IBProvider was to be paid for, but taking into account that we needed it for a short time, we decided to use the trial version or even Firebird ODBC Driver.

After the linked server had been created, we discovered that the ODBC driver was unable to cope with quite large amount of data, we are not talking about millions of entries, just several dozens of thousands. The queries directly uniting data from the two DBMSs were run disastrously slowly. We were trying to create synonyms — it was getting better. Then we decided to turn queries into presentations and use these presentations when forming queries. Good, but not enough. A query was still run for several minutes. What was the problem?

We decided to test IBProvider. Let it be the trial version. If everything was ok, we would buy it. Wow! The same query was run in several seconds. We couldn’t believe it, it was perfect! We compared the data and corrected the differences. The trial period was finished. Moreover, we decided to transfer the new information system Vostochnyi Express to new x64 server. We though this provider was not suitable for x64. Nevertheless, we read instructions and tried. And it worked!

I chose IBProvider because it was the only method that helped us solve our problem.

The following IBProvider features were useful for me:

• Support of all versions of Firebird/InterBase servers
• Easy scalability, support of 64-bit applications
• MS SQL LinkedServer technology and updatable rowsets technology
• Integrated data type converter

Description of the working system:

• MS SQL Server 2005, up to 500 connections
• Firebird 2.0, up to 10 connections yet

With IBProvider, we successfully solved the set task of executing queries from the heterogeneous environment. It allowed us to avoid export/import of data and to increase the speed of operations on eliminating the differences between the bases with different DBMSs.