Tags: SQL Server Reporting Services
A large European company decided to migrate their Oracle database system to SQL Server. Of course, they wanted to outmaneuver the performance of their original system. The original customer’s system included a huge bunch of over 700 reports, built in deprecated Oracle forms technology.
We offered a solution on the new SQL Server Reporting Services platform. This solution satisfied the rigid performance demands of the customer. We used some previously developed tools to automate the Oracle Forms conversion process, but after all, we performed a lot of manual edits.
Learn more about the technical background of this project from the following video.
Expensive Oracle and Other Issues of the Original Customer’s System
Let’s take a look at the original customer’s system. They were using a rather expensive Oracle database instance together with an outdated Oracle Reports, created using Oracle Forms.
This solution couldn’t provide great usability because it lacked a web-interface. So, when our customer wanted to install an application to their clients’ machine, they needed to install the Oracle environment first. What’s more, they faced using a specific and rather old version of the browser to generate the reports.
The future prospects of the customer’s system remained unclear because Oracle ceased to support their version of Oracle Reports. Also, our customer required qualified specialists who could support and upgrade the existing database system.
The customer has been developing the original database system for several decades. Various developers contributed by adding the reports when needed. As a result, our customer could not provide any documentation for his reporting service. Neither could he understand the business logic standing behind some of the reports. The customer initially provided us a whole lot of over 700 reports “as-is”. They were expecting to keep the performance rate after conversion to the new environment.
Summing up, the customer decided to migrate his database from Oracle and update his reporting service. Actually, the customer received an irresistible offer from Microsoft that influenced their decision to choose SQL Server as a target database platform. Then Microsoft recommended DB Best, their Gold Partner for 7 consecutive years, to complete this project.
Database Migration
Typically, we approach the database migration process in 12 steps. We used SQL Server Migration Assistant (SSMA) to automate the conversion of database schemas. Then we migrated a smaller part of customer’s data. That was just enough to test how the reports work in the new environment.
After this, we proceeded to the most difficult yet the most interesting phase of the migration project. We mean conversion of over 700 Oracle Reports to SQL Server Reporting Services.
Background on Oracle Forms Conversion
Loads of our customers wanted to migrate from Oracle Forms. We have dealt with this task for several times. So, we decided to create a complex approach for us to use with any sort of Oracle Forms migration. In addition, we developed an automated tool allowing fast Oracle Forms conversion to modern platforms (SQL Server, Azure, Amazon Web Services, etc.).
Summing up, our automated tool works as shown in the following video:
We talked about reports conversion in the end of the video. So, we’ve got a specific Oracle Reports Conversion Assistant (we name this tool ORCA). This tool helps us automate a major part of reports conversion routine.
Typically, the Oracle Reports conversion workflow looks as shown in the picture below.
Regardless of the complexity of the report, we can guarantee that after manual processing the converted report will be identical to the original one.
So, now let’s explore how we completed the Oracle Reports conversion in this particular case.
Reports Conversion
We approached this task in 2 steps: design replication and performance optimization.
Firstly, we created the reports in the SQL Server Reporting Services with designed identically to the original Oracle’s reports. Assuming the complex structure of some of reports, we had to complete a lot of manual work on the automatically generated code.
The customer approved the design of the reports in the new database environment swiftly enough. But the reports’ performance still didn’t satisfy them. So we had to fiddle with some code optimization in order to meet the strict customer’s demands.
The reports generation took too long because the source code was initially developed to fit Oracle best. The same code structures perform badly on SQL Server, so we had to optimize them. In fact, we developed the major part of the reports’ code from scratch.
At this stage, we faced the following problem: while running the report conversion for several times in a row, all previously made manual edits are dismissed.
Because of this issue, we managed to deploy no more than 5 optimized reports a week. Eventually, we have found a reasonable solution allowing us to increase the release rate by 10 times.
Results and Benefits
After we converted all reports to SQL Server Reporting Services and the customer approved their design and performance, the system was ready to launch in the new database environment. We migrated the rest of the data to SQL Server and deployed the new database instance in production.
At the same time, we developed the documentation on the newly created reports in the SSRS and provided it to the customer. This helps simplify further modernization or upgrade of their system.
So, we breathed a new life into old Oracle Reports. Besides, the customer reached the following benefits:
- Easy-to-use client’s WEB-interface of the new reporting system;
- Less expensive SQL Server licenses compared to Oracle ones;
- A modern database system meeting the latest industry standards.
