ABCloudz recently helped The Morning Star Company, one of the world’s largest producers of tomato products, migrate their Oracle database to SQL Server. The Morning Star Company operates three processing facilities in California, handling nearly 10% of the global tomato production. Their IT infrastructure included both Oracle and SQL Server databases, but due to the high cost of Oracle licenses, the company decided to gradually retire their Oracle servers and migrate to SQL Server. They reached out to ABCloudz for assistance with migrating their first Oracle database, ensuring a seamless transition without disrupting their business operations.

Read on to learn how we successfully migrated this database while maintaining the integrity and functionality of interconnected systems within Morning Star’s infrastructure.

Initial client infrastructure and migration objectives

Morning Star’s infrastructure was a complex mix of Oracle and SQL Server environments. The first target for migration was a mission-critical Oracle database that served as the operational backend for their CRM system, tracking everything from tomato harvests and shipments to customer orders. This database was heavily interconnected with other servers within the company’s IT infrastructure, including a reporting server running SQL Server, which was dependent on this Oracle database, pulling data through a DB link to generate reports. The reporting server also retrieved data from other servers within the client’s infrastructure, but those connections were out of scope for this migration project.Our initial analysis identified this dependency, which meant that migrating the Oracle database required simultaneously updating the SQL Server reporting procedures that relied on the DB link to ensure uninterrupted reporting functionality.

Migrating Oracle DB and reporting components

We began the migration by moving the Oracle database to SQL Server using the SQL Server Migration Assistant (SSMA). (By the way, to highlight our expertise, our team originally developed a migration solution that was later acquired by Microsoft and became the core of SSMA.) The Oracle database included 18 schemas, 367 tables, 235 views, 291 packages, 179 standalone procedures, 42 standalone functions, 61 sequences, 1321 synonyms, 16 triggers, 16 user-defined types (UDT), and 24 jobs.

Using SSMA, we were able to automatically convert a significant portion of the database schema and migrate the 120 GB of operational data to the new platform. However, not all Oracle-specific components were converted automatically, so we manually adjusted certain objects. The complexity of this manual conversion was moderate, with particular challenges in migrating the logic for file transfers over FTP and SMTP protocols.

Our 12-step database migration methodology guided the entire process, ensuring a smooth transition. This methodology covers everything from initial envisioning and assessment to schema conversion, data migration, and post-production support, enabling us to address all potential challenges and minimize disruptions to business operations.Simultaneously, we worked on the SQL Server reporting environment. As we mentioned earlier, the reporting server did not store data itself; instead, it housed stored procedures that executed SQL queries to pull datasets through various database links and pass them to SQL Server Reporting Services (SSRS). Initially, the client provided a list of 482 stored procedures for review. After further analysis and discussions, we determined that only 294 of these procedures required migration, as the others were either not in use or did not reference the Oracle database.

We redirected the SQL statements in these 294 procedures to point to the newly migrated SQL Server database. Additionally, we reconfigured one linked server to establish connections with the new environment. The complexity of this work was moderate, as many procedures contained complex SQL logic that required manual adjustments to ensure compatibility with the new database structure.

Throughout the entire project, we applied various SSMA tips and tweaks to optimize both the automatic and manual conversion processes. These techniques allowed us to address challenges such as schema mapping, data type conversion, and overall performance optimization. For more information on the specific techniques we used, please refer to our related blog posts:

These resources provide detailed insights on managing complex database migrations effectively.

Testing phase for ensuring consistency and stability

Our testing phase involved parallel testing of the original and migrated environments. We set up two sets of servers: the original Oracle and SQL Server environment, and the new SQL Server-only environment. For each test case, we ran identical reports with the same parameters on both sets of servers and compared the outputs. Any discrepancies were analyzed, and adjustments were made to the migrated environment as necessary.Once internal testing was completed, we proceeded with a test migration within the client’s environment. Due to access restrictions, we couldn’t test some of the more complex dependencies involving multiple servers within the client’s infrastructure. Morning Star’s IT team took over testing for these dependencies, identifying a few additional issues that we promptly resolved. After the client verified that all systems were functioning correctly, we received approval to proceed with the final production migration.

Post-production support

After the production migration was complete, we transitioned to a post-production support phase. During this period, we provided immediate assistance for any issues reported by the client. Most errors were related to data entry inconsistencies, so we enhanced the data entry interfaces to minimize human error and prevent such issues from occurring in the future.

Conclusion

The successful migration of Morning Star’s Oracle database to SQL Server marked the beginning of their broader initiative to consolidate all their databases onto a single platform. This project enabled them to reduce licensing costs and streamline their operations without any disruption to critical business functions.

With hundreds of successful database migration projects in our portfolio, ABCloudz has extensive experience in delivering reliable solutions for complex scenarios. If you’re looking for assistance with your database migration, contact us today to see how we can support your business needs.

Contact us