Oracle to SQL Server Migration SSMA for Sweetwater Union High School District

Organization

Sweetwater Union High School District ensures a safe, inclusive, collaborative culture that empowers each student to actively engage in a meaningful educational experience to pursue personal and academic success. The district’s 32 campuses are located across various cities. Founded in 1920, the Sweetwater District has grown to more than 36,000 students in grades 7 through 12 and more than 10,000 adult learners.

Challenge

The project presented several key challenges inherent in migrating a critical legacy system for a large public education system:

• Outdated Source System: The reliance on an older version of Oracle Database introduced potential compatibility hurdles with modern migration tools and the target SQL Server 2022 environment.
• Significant Data Volume: As a large educational institution in the San Diego area, the customer possessed a substantial amount of historical data. Migrating this extensive dataset while maintaining data integrity and minimizing downtime posed a significant logistical and technical challenge.
• Limited Source System Expertise: The customer’s internal team had limited familiarity with the intricacies of their legacy Oracle system, potentially hindering the identification of critical data dependencies and the extraction of necessary database objects.
• Time-Sensitive Knowledge Transfer: The impending departure of the customer’s current Oracle consultant created a critical time constraint for understanding the source system and executing the migration, necessitating an accelerated and efficient approach.
• Lack of In-House Oracle Migration Expertise: While the customer had experienced SQL Server DBAs, the team lacked specific expertise in migrating data and database objects from an Oracle environment to SQL Server, requiring external expertise to ensure a successful transition.
• Unknown Migration Scope: The initial uncertainty regarding the precise volume of data and the complexity of database code (views, procedures) requiring migration to SQL Server introduced a risk of underestimation in project planning and resource allocation.
• One-Time Migration Requirement: The expectation of a single, comprehensive data and code transfer amplified the need for meticulous planning and execution to avoid data loss or the need for iterative migration cycles.

Solution

Oracle to SQL Server Migration Summary

The migration process involved upgrading the source Oracle database to a version compatible with the SQL Server Migration Assistant (SSMA) for Oracle (Microsoft documentation: https://www.microsoft.com/en-us/download/details.aspx?id=54258). Based on client specifications, a targeted subset of Oracle schemas and tables was selected for migration.

The target SQL Server environment was prepared by installing the latest version and verifying critical configuration settings. These included enabling Instant File Initialization, optimizing memory allocation, ensuring a 64K block size for data volumes, and validating underlying virtual machine configurations for optimal SQL Server performance.
The Oracle-specific version of SQL Server Migration Assistant (SSMA) was deployed and configured to connect to the source Oracle instance. Utilizing the client-provided list, SSMA was configured to extract the designated schemas, tables, views, and stored procedures.

An initial assessment using SSMA’s evaluation report identified potential conversion challenges between the Oracle and SQL Server platforms. All reported errors and warnings were subsequently addressed, ensuring a smooth transition.
SSMA was then employed to generate the necessary Data Definition Language (DDL) scripts for creating the corresponding database objects within the target SQL Server instance. Following object creation, the data migration process for the identified subset commenced. Estimated migration timelines were established and communicated proactively.

Throughout the entire migration lifecycle, daily status updates were provided to the client team. Post-migration, the importance of SQL Server-specific optimizations, such as index creation and performance tuning, was emphasized due to the inherent architectural differences between the database engines. Verification confirmed that Query Store was enabled to facilitate performance analysis and tuning.

To enhance transparency and awareness, detailed updates via spreadsheets were provided to include schemas and objects migrated. This established a document as a common frame of reference to track progress and identify gaps early in the process. In cases where modification of data types from Oracle to SQL Server was necessary, these were documented. For table data migrated, details included row counts migrated with validations via SSMA Migration Report and additional verification via row counts on the target SQL database tables for further confirmation. The spreadsheets also included schemas and objects not included in the migration. The intent of this information is to address “Unknown Migration Scope” by establishing a common frame of reference for all involved with this effort.

BUSINESS RESULT

The Oracle database migration was successfully completed, enabling the customer to proceed with the decommissioning of their legacy Oracle system. This timely migration minimizes operational overlap and associated costs. The migrated data now resides on a modern, supported SQL Server platform, providing a foundation for improved performance, scalability, and integration capabilities. By addressing potential conversion issues proactively and optimizing the SQL Server environment, the project ensures a stable and efficient database infrastructure for future business operations. The successful migration and subsequent decommissioning of the legacy system contribute to reduced maintenance overhead and allow the customer to focus resources on strategic initiatives, leveraging the new SQL Server environment.

XTIVIA needs the contact information you provide to us to contact you about our products and services. You may unsubscribe from these communications at anytime, read our Privacy Policy here.