Oracle to SQL Server Migration of VLDB (Very Large Database) with minimal cutover outage
Blue Crystal Solutions (BCS) was engaged by a client to migrate Oracle VLDB to SQL Server with minimal business impact when the databases were cutover from Oracle to SQL Server.
The customers application imports csv source files into the database but does provide business logic which allows the application to determine whether these files have already been processed. BCS used Microsoft Visual Studio and SSDT (SQL Server Data Tool) to create an SSIS project to generate the packages that provided the functionality to copy the data from Oracle to SQL Server. These packages were executed prior to the cutover and imported a large percentage of the data, then the application processed these backlogs of csv source file to synchronize the Oracle and SQL Server databases.
Any dirty data had to be cleansed due to the configuration difference between the Oracle database and SQL Server’s handling of upper and lower case of characters so the SSIS project had to identify all the dirty data to allow these to be fixed within the Oracle database and then the import process re-run for the table.
Data limitations exist in the SSIS project since the Oracle VLDB contains tables which had over 1B+ of rows causing the import to stop when this threshold was reached. As a workaround, dynamic select queries had to be implemented into the packages for the partition of these table. Cumulatively these partitions had to be less than 1 billion rows when imported at a time. Additionally, this improved the overall process time to import these tables as this allowed multiple SSIS projects to run in parallel of one another.
