Tags: code conversion
One of the largest US educational organizations has been running their data handling system in Oracle environment. They wished to reduce maintenance costs due to expensive Oracle licensing model, which includes an additional fee for the partitioning feature.
We provided the solution of migrating the customer’s database to SQL Server. Reconstruction of the original partitioning system in SQL Server allowed us to save extra money in addition to significantly lower SQL Server maintenance cost compared to Oracle.
Check how we performed the migration to SQL Server and thus saved our customer’s money in the following video:
Original customer problem
Our customer has been supporting a huge student records system. They needed to use Oracle partitioning to improve the performance for loading data. As a result, they had approximately 4000 partitions in the original database. Thus, we needed to re-create the original partitions in the new version of SQL Server database.
After analyzing the original database, we started to convert Oracle database schema to SQL Server.
Our solution
We applied SQL Server Migration Assistant (SSMA) to automate the conversion process. In that case, SSMA was capable of converting over 90% of the source code in fully automatic mode, which is a great result.
For the manual conversion, the majority of the effort was around PIVOT clauses and dynamic SQL statements that SSMA couldn’t handle. We rewrote this unsupported statements according to SQL Server standards. Discover how we can handle this typical conversion issue from the following blog article: Oracle to SQL Server migration with SSMA: How to convert Unparsed SQL — Pivot Operator
SSMA tool contains an extension pack that allows emulating Oracle functions in SQL Server in order to perform the migration. However, automatically emulated functions can reduce the performance of the newly created SQL Server database. To prevent this and therefore increase the performance, we manually created native SQL Server functions.
We paid particular attention to the table partitioning. In Oracle, dynamic partitioning is performed automatically using standard instruments. SQL Server has another partitioning mechanism. That is, first we had to discover the original partitioning scheme. Then we re-created this scheme using SQL Server instruments. Finally, we delivered T-SQL script that created the new partitioned table being updated with new information on a daily basis.
We ended up with performing the functional testing, where we ensured that queries in the migrated database return the right results.
With our solution, the customer reduced the costs by taking advantage of less expensive SQL Server database. Moreover, we optimized the new database performance and developed the whole solution within a short time.
Feel free to contact us to complete your migration project and save your money.
