Modernizing advanced engineering application from Oracle to SQL Server
Proprietary database features often create significant obstacles for developers. To demonstrate as an example, we will take a look at one of Oracle’s most valuable proprietary features – Oracle Call Interface (OCI). It ensures efficient communication between the database and the application code. Surely, you may use other options for connecting to Oracle. However, once you have committed to OCI, switching to another interface becomes a real headache. Read our new blog post to understand how you can overcome challenges related to Oracle to SQL Server app modernization.
The Problem
We will use a practical example from ABCloudz past experience to explain the process of modernizing an application from Oracle to SQL Server. One of our clients had been using the Oracle database in their Windows-based product for a long period of time. At some point, the client decided to switch to a more cost-efficient solution. It was imperative for the solution to integrate well with the Microsoft technology stack. For these reasons, the client-focused on modernizing the app to SQL Server.
Customer application
The client’s application models the control logic of a power plant. It may be used to model “control points” of a nuclear plant. Each control point has a corresponding physical element within the plant. For example, there are control points for shutoff valves and temperature gauges. The application allows the engineer to obtain the essential details from different control points thus forming a broader picture and helping operate the power plant more effectively. The described application is responsible only for modeling. It is connected to another computer system, which uses the obtained model to operate the power plant as seen on the picture below:
Main challenges
There were no significant challenges pertaining to the database migration. The most problematic aspect of this project was converting the application code properly. The application code was rather sophisticated. The customer’s product comprised upwards of a million lines of code. Most of the code was written in C++ which used OCI extensively. The application also contained C# code, which used ODP.NET (Oracle version of ADO.NET).
Also, the database was accessible through ODBC and SQL*Plus scripts for good measure. Our technology specialists had to consider all such features while modernizing from Oracle to SQL Server.
There was also a challenge with the app’s QA testing. Due to its sophisticated structure, the application required specialized engineers to execute it to ensure all components are tested properly. As a result, ABCloudz QA specialists implemented automated database testing and API testing while the customer handled the app’s functional testing.
Check the image below to understand the impact of modernization to the application
Solution Process
At ABCloudz, we apply a 12-step migration process to ensure the best quality of complex app transformations. Here are the basic stages of this process:
1. Envisioning and assessment
At this step, we analyze the app’s future state and determine basic architecture features for achieving it. Besides, we outline the following 11 steps to ensure the most efficient modernization from Oracle to SQL Server.
2. Database schemas conversion
This stage involves rewriting the database tables, views, stored procedures, triggers, and functions for SQL Server. Most such processes are handled by automation tools, while 15-20% of work has to be completed manually.
3. App conversion
Technology specialists change the app’s dependency from Oracle to SQL Server. The app conversion includes many actions that are enumerated below.
4. Scripts conversion
This step involves taking care of regularly scheduled or ad hoc jobs, report creation, and data exchange with external apps (ETL). Different apps may have different scripts, such as Linux shell scripts or Perl scripts. In addition, remediation might involve the use of a new platform (for example, Windows) or a different implementation (for example, SSIS).
5. Integration with third-party apps
At this stage, database migration specialists establish the app’s connectivity with any external interface related to the database. Examples of such interfaces include third-party apps or drivers.
6. Data migration
This step involves moving the data from Oracle to the new SQL Server. You may face challenges in case there is an extensive database and a very small cut-over window. You might need to use incremental methods or tools for such cases.
7. Functional testing
Tech specialists review whether all application functions work properly after the migration.
8. Performance testing
This stage allows you to determine the migration’s impact on your app performance. Often, database modernization moves performance bottlenecks from one software part to another.
9. Rollback planning and deployment
Software engineers create a rollback plan for the migrated app before deploying its modernized version.
10. Documentation
At this stage, you receive all the required documentation on your modernized app.
11. Knowledge transfer
This is when you and your employees learn how to take full advantage of your app after the database migration.
12. Post-production support
It goes about the ongoing software development process, as you should constantly update and optimize your platform.
Architecture of the solution
Now, let’s get back to our previous example. One of the project’s largest challenges was successfully replacing the OCI. We identified two main implementation options:
1. To replace the 26 OCI calls used in the code.
2. To change the code using OCI and use ODBC instead.
Eventually, we decided to focus on the second option. We decided so because implementing the OCI calls against SQL Server would create an unnatural fit.
The image below illustrates the current architecture.
As you may see, different components of the application access the database in different ways. For example, some connections, such as DCOM, resulted from networked architecture. We took it into account while developing the future architecture of the modernized app. The image below illustrates our vision.
The illustration above shows new or changed components of software architecture as white boxes. They included the substantial impact to the customer’s DB API layer, along with the other parts for database access. ADO.NET became a drop-in replacement for ODP.NET. As for the existing ODBC connections, they were not affected beyond remediating queries and replacing drivers. Our software engineers also replaced embedded PL/SQL queries in all applications. In addition, we rewrote some maintenance and data loading scripts that used SQL*Plus since they did not fit the application architecture. Finally, we updated the app’s installation process with a silent database install.
Issues with converting the database
We used SQL Server Migration Assistant (SSMA) as the basic tool in the process of database conversion. SSMA automates the migration of database structure and code. Database conversion with SSMA still requires manual effort since the given tool works on default assumptions about data types mapping, naming conventions, and the best methods for translating functional behavior. Even though SSMA is a perfect tool for Oracle to SQL Server migration, some conversion issues have to be fixed manually.
Our technology specialists also needed to study actual data in the customer’s production database since it enabled them to determine the best data type mapping. Mapping Oracle NUMBER and DATE types was a significant challenge, as these types are quite different in SQL Server. On a positive side, SSMA enabled us to modify default mapping types and choose among the available alternatives.
We also needed to change SQL Server’s explicit transaction mode. Its default transaction model was auto-commit, which did not allow us to commit and rollback explicitly. Besides, we had to cope with the differences in database trigger handling. For example, Oracle allows multiple INSTEAD OF triggers, while there can be only one trigger in SQL Server. Also, SQL does not support the BEFORE trigger, which is peculiar to Oracle.
Another issue was that the app relied on the storage of tree structures. Hence it used the CONNECT BY clause in Oracle. Meanwhile, this clause looks completely different in SQL Server as Recursive Common Table Expression (CTE). Basically, this difference impacted the look of the code, but it could also cause performance differences in some cases.
Issues with converting the app
Our database migration specialists also had to solve numerous challenges pertaining to application migration. For example, the application’s DB API layer operated in part as a query constructor. Its clauses were layered incrementally. In this case, we needed to bind the parameters that were handled differently in OCI and ODBC.
We also needed to tease out the intent behind every cursor use. The challenge was caused by the different semantic nature in SQL Server and Oracle databases. Finally, our specialists retained the functionality of calling through to Oracle and built a logging infrastructure while converting the DB API layer. As a result, we could easily test the units by comparing precisely what was passed on to Oracle database through OCI and what was passed on to SQL Server through ODBC.
Final thoughts
Do you still think that changing a big C++ app from using OCI on Oracle to ODBC on SQL Server is frightening? Our practical example demonstrates that it is challenging but, still, very doable. Our Oracle to SQL Server modernization enabled the client to supply an application in one tidy package that employed only Microsoft products for data analysis and various operations.
Was this article helpful? If you have any questions please make sure to contact us. We are ready to help you sync your application modernization goals with our database migration expertise.
