One of the largest educational organizations in the United States had been running their data handling system in the Oracle environment. They wanted to reduce maintenance costs due to the expensive Oracle licensing model, which includes an additional fee for the partitioning feature.
The ABCloudz team provided a solution for migrating the customer’s database to SQL Server. Reconstruction of the original partitioning system in SQL Server allowed us to significantly lower SQL Server maintenance costs compared to Oracle.
Check out how we performed the migration to SQL Server and 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 the Oracle database schema to SQL Server.
Our solution
We applied SQL Server Migration Assistant (SSMA) to automate the conversion process. In this application, SSMA was capable of converting over 90% of the source code in fully automatic mode, which is a great result.
For the manual conversion, most of the effort was around PIVOT clauses and dynamic SQL statements that SSMA couldn’t handle. We rewrote these unsupported statements according to SQL Server standards.
SSMA tool contains an extension pack that allows for 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.
Our specialists paid particular attention to the table partitioning. In Oracle, dynamic partitioning is performed automatically using standard instruments. SQL Server has another partitioning mechanism. First, we had to discover the original partitioning scheme. Then we re-created this scheme using SQL Server instruments. Finally, we delivered a T-SQL script that created the new partitioned table being updated with new information daily.
We ended by 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 a less expensive SQL Server database. Moreover, we optimized the new database performance and developed the whole solution in a short time.
Feel free to contact ABCloudz to complete your migration project and save your money.
