As moats create obstacles in the way of an invader, so do proprietary database features for developers. One of Oracle’s proprietary features is the Oracle Call Interface (OCI) which provides efficient communication between application code and the database. There are other ways to connect to Oracle, but once you have committed to OCI, you are “protected” from switching to another database. In this blog post, we break down an application modernization from Oracle to SQL Server for an advanced engineering-oriented customer.
Customer Problem
Our customer had been using Oracle in their Windows-based product for many years, and they came to us with a desire for a more cost-effective solution that integrates well with the Microsoft technology stack.
Application Function
The customer’s application is used to model the control logic of a power plant, such as a nuclear plant, in terms of “control points.” Each control point corresponds to physical elements within the plant, like temperature gauges and shutoff valves. An engineer will enter the details of the different control points and relate them to each other to orchestrate the correct operation of the power plant. This software only does the modeling though; another computer system operates the plant after receiving a download of the model as shown in the illustration.
Solution Challenges
The database was not overly complicated to migrate; the main challenge was converting the application code.
The application itself is extensive, comprising upwards of a million lines of code, mostly in C++ which uses OCI extensively. There is also C# code which uses ODP.NET (Oracle’s version of ADO.NET), and the database is also accessed through ODBC and SQL*Plus scripts for good measure. This was the bulk of the effort for this modernization from Oracle to SQL Server.
Testing the application was a separate challenge because it required specialized engineers to exercise it into all the corner cases properly. DB Best was able to highly automate database testing and API testing but relied on the customer for functional application testing.
The impact of modernization to the application can be illustrated like this:
The closer you get to the database, the more significant the effect. In the application code proper, only embedded PL/SQL had to be remediated.
Solution Process
DB Best has a process for dealing with complex application transformations like this using a 12-step migration process which ensures that we cover all the bases and generally lays out the conversion flow:
- Architectural design
- Database schema conversion
- Application conversion
- Scripts conversion
- Integration with 3rd party applications
- Data migration
- Functional testing of the entire system
- Performance tuning
- Integration and deployment
- Documentation and knowledge transfer
- Project management and version control
- Post production support
Architectural design is a detailed analysis description of the future state and how we get there. There is much more to a modernization from Oracle to SQL Server for an application than just changing a few queries. The 12 steps outlined below will touch on all the aspects of the work.
Database schema conversion is required to make a new home for both the data and any procedural code in the Oracle database. The structure of the database tables and views, and all stored procedures, functions and triggers need to be rewritten for SQL Server. While there are tools to automate this process, there is typically 15-20% left to do manually.
Application conversion will change the application dependency on Oracle to SQL Server, and in this case, we could limit our conversion focus to a C++ library layer between the application and the OCI API. The main reason for this library layer was to support not only Oracle but also another database technology with an overlapping purpose. More on this conversion below.
Scripts conversion takes care of ad hoc or regularly scheduled jobs to maintain the database, create reports and exchange data with external applications (ETL). Depending on the application, these scripts could be Linux shell scripts, Perl, or anything under the sun. The remediation may involve both a new platform (e.g., Windows) and possibly a different implementation (e.g., SSIS).
Integration with third-party applications consider any database related interface not controlled by the modernized application. It could be a matter of different drivers or configuration in the third-party applications to accommodate SQL Server.
Data migration will create or select a method for moving the existing data in Oracle to the new SQL Server. Sometimes this can be a challenging problem if the database is extensive and the cut-over window very small. In those cases, an incremental method or tool may be required.
Functional testing of the entire system is self-explanatory.
Performance tuning is vital since the radical shift of database technology tends to move performance bottlenecks from one place of the product to another.
Integration and deployment, Documentation and knowledge transfer, Project management and version control and Post production support are self-explanatory.
Solution Architecture
In this project, the most prominent “rock” was how to replace OCI. We considered two options:
- Replacing the 26 OCI calls used in the code (small set compared to full API)
- Changing the code using OCI to instead use ODBC
Our initial approach of replacing the OCI calls was abandoned in favor of the second option because the implementation of the OCI calls against SQL Server was an unnatural fit.
The current architecture looked like this:
As can be seen in the above illustration, different application components are accessing the database in different ways. Some of the more esoteric connections like DCOM were a result of a networked architecture.
And after careful consideration, the future architecture after a modernization from Oracle to SQL Server was drawn like this:
In the illustration above, the white boxes represent new or changed components which included the substantial impact to the customers own dbapi layer and other parts for database access. ADO.NET was pretty much a drop-in replacement for ODP.NET, and the existing ODBC connections were not affected beyond replacing drivers and remediating queries. Embedded PL/SQL queries were replaced in all applications. We needed to rewrite some data loading and maintenance scripts which used SQL*Plus. Finally, we had to modify the installation process of the application which includes a silent database install.
Database Conversion Issues
Central to the database conversion process is the SQL Server Migration Assistant (SSMA) which can be downloaded for free from Microsoft. This tool is capable of automatically migrating a large amount of the database structure and code, but it works on default assumptions regarding naming, mapping of data types and the best ways to translate functional behavior. Because of this, SSMA is a perfect tool for this modernization from Oracle to SQL Server.
Our experienced migration team needs to study actual data held in the customers production database to determine the best data type mapping. Examples of mapping challenges include the Oracle NUMBER and DATE types which are quite different in SQL Server. In SSMA you can modify the default mapping of types and chose among alternatives.
The default transaction model in SQL Server is autocommit, but if you need to be able to commit and rollback explicitly, you need to change SQL Servers explicit transaction mode. In our case, the dbapi layer wanted to explicitly control what is under a single transaction and not delegate this to the database.
There are differences in trigger handling and Oracle, for example, allows multiple INSTEAD OF triggers while SQL has only one and Oracle has a BEFORE trigger which SQL Server does not.
This application relies on the storage of tree structures and therefore in Oracle use the CONNECT BY clause which looks very different in SQL Server as Recursive Common Table Expression (CTE). Mostly this impacts the look of the code, but in some cases, there can be performance differences as well.
Application Conversion Issues
The application’s dbapi layer operates in part as a query constructor where clauses are layered on incrementally. One challenge was binding of parameters which is handled differently in OCI, which relied on names, and ODBC which relied on position.
Another major challenge was teasing out the intent behind every use of cursors which created problems due to their different semantic nature in Oracle and SQL Server.
During the conversion of the dbapi layer, we retained the functionality of calling through to Oracle and instituted a detailed logging infrastructure. This allowed us to compare precisely what was passed on to Oracle through OCI and what was being passed through to SQL Server through ODBC. This greatly improved the quality of unit testing.
Conclusion
While it may seem daunting to change a large C++ application from using OCI on Oracle to ODBC on SQL Server, it is worth while, and very doable. After this successful modernization from Oracle to SQL Server, the customer can now supply this design software application in one tidy package with only Microsoft product dependencies for operations and data analysis.
