Multi-Site Database Migration

A large company with 430+ remote sites wanted to migrate their Oracle Rdb database to open source PostgreSQL. The application was to remain on an OpenVMS server; however the database was going to be located on a Linux server.

What technical problems did that raise?

A feasibility study was undertaken to identify the technical challenges of the project and how they might best be resolved. The initial technical challenge was to determine how the existing Oracle Rdb SQLMOD-based interface and associated code could be converted as efficiently as possible into code that could be used in conjunction with PostgreSQL. To solve this problem, our migration partner Sector7 were able to provide tools that could convert the SQLMOD into equivalent C functions containing embedded SQL statements that were compatible with the PostgreSQL embedded SQL pre-processor. The Sector7 tools were able to achieve 100% automated conversion of this code.
Another technical issue to be resolved was how the OpenVMS-based application was going to communicate with the Linux-based PostgreSQL database. Here VSI stepped in and ported the PostgreSQL client to VSI OpenVMS. While the client was initially ported for the customer in question, the ported client is open source software and is freely available for download from the VSI website.
The scale of the migration was considerable, involving some 430+ sites worldwide and a timescale of approximately two years, equating to the migration of around 4 sites per week (on average); a punishing rollout by any standard, and all to be performed with no disruption to business. Once Sector7 and VSI had developed the tools and processes for migrating the data, the customer took over the task of migrating the sites, with remote and on-site resources combining to deliver a successful project on-time and to budget. Operationally, the customer could not afford to have any of their remote locations idle while the migration took place, so the actual migrations took place outside of normal operating hours.

What are the benefits

Moving to an open source database has provided the customer with number of clear benefits, both commercial and technical.

From a commercial perspective, it means there are no license costs to pay, and this can be a significant figure with large numbers of databases and database servers involved (as with this customer). There are of course still support costs, but these too are reduced, and due to the much wider adoption and open source nature of PostgreSQL relative to Oracle Rdb, skilled database administrators and other such resources are more readily available, while from an application development perspective of view little has changed, allowing the current development team to continue to maintain and support the OpenVMS-based application largely as they have always done. By definition legacy proprietary databases have a fixed pool if resources, generally employed or sub-contracted by the company that developed the product, leading to a restricted pool of knowledge and inflated pricing, because there often isn’t anywhere else to go.

Finally, in this particular instance there were actually performance improvements observed for most database operations. This was a bonus to the project as it wasn’t an original aim for the migration.