Change the master database to MS SQL Server
As part of a major initiative involving the implementation of several new Microsoft-based applications, this government department wanted to standardize on using Microsoft SQL Server for data storage wherever possible. While it was not practical or necessary to move all data used by OpenVMS-based applications from Oracle Rdb into Microsoft SQL Server, it was necessary for OpenVMS applications to access and update a variety of data stored in SQL Server, and to do so in a reliable, performant, and secure manner. The open source FreeTDS library was used to facilitate these requirements, with changes made to OpenVMS-based C and COBOL code to include appropriate calls to the FreeTDS API. The interface has performed flawlessly for many years and continues to do so. It should be noted that the implementation of this interface was done manually, as opposed to developing an embedded SQL pre-processor that could generate the FreeTDS API calls automatically. The scale of this particular project did not warrant the development of such a tool, and the highly dynamic nature of most of the SQL queries would have largely precluded the use of a pre-processor.
What technical problems did that raise?
The primary technical challenge for this project was identifying a solution that could be used on OpenVMS to facilitate the required integration with Microsoft SQL Server, and that could do so in a reliable and performant manner. Porting the open source FreeTDS API to OpenVMS is relatively straightforward; however, the API is designed to be called from C as opposed to COBOL. It was therefore necessary as part of the project to implement a simple wrapper API on top of FreeTDS that could be more readily used with COBOL. This was not a significant technical challenge; however, a good understanding of the C and COBOL and the differences between them in terms of data types and argument passing mechanisms was required to successfully implement the solution.
What are the benefits?
The project illustrates the use of open source software to provide a cost-effective high-quality data integration solution, and is illustrative of the fact that it is now always necessary to use often expensive proprietary solutions to implement this type of functionality. The main benefit to the customer was therefore being able to solve their data integration problem in a cost-effective and straightforward manner that was able to meet both their functional and non-functional requirements.