Tags: PowerShell, SQL Server Integration Services
A global financial software provider needed to migrate their billing solution from Oracle to SQL Server for one of their strategic clients located at the other end of the world. The actual migration had to be performed by the remote team. In order to do so, our customer was looking for an automated solution with detailed step by step instructions.
To meet the customer’s needs, we developed a command prompt migration utility accompanied by extensive documentation for a remote team. With our solution, the customer was able to migrate all data within the designated outage window by themselves without any issues.
Check the features of our migration solution in the following video.
In our solution we used SQL Server Integration Services to automate data migration. We extracted data from source Oracle database and saved it as SSIS packages (one SSIS package per table). Then we developed PowerShell script to automate the execution of packages. That way allowed our customer to load the data to the target SQL Server database in an automatic mode.
High-level Oracle to SQL Server migration steps
Let’s overview the steps that we provided to the customer for creating their own database migration foundry:
- Deploy the target SQL Server database on the newly provisioned SQL Server production environment. We provided them a backup file to restore from it and thus to create an empty database with the necessary schema structure.
- Test the connectivity to the source and target databases using Oracle SQL Developer and SQL Server Management Studio.
- Migrated the data from all tables hosted in source Oracle database using our command prompt migration utility. In the command prompt window, they need to select the SSIS packages they want to execute. We provided various options such as the migration of all tables in the database or just one specific table. Once the execution of all selected SSIS packages was completed, we provided a migration iteration label that was displayed in the command prompt. This label is needed for future data validation.
- For the migration validation phase, review the migration logs to ensure that no errors occurred during the migration process. Then compare the table row counts for all migrated tables between the source and target databases. The customer then verified that key query results sets in the migrated database return the right data. They did his by executing queries using Oracle SQL Developer and SQL Server Management Studio. That way they can compare the results from source and target data sources.
- Once the migration process completed, they creating a full database backup using SQL Server Management Studio.
Additional features
Additionally, we prepared detailed instructions with reference screenshots to cover the most common migration errors. Using our instructions, the customer could easily handle SSIS packages execution errors and recover in case of possible database corruption.
Another interesting feature of our solution is the ability to control the amount of SSIS packages executed in parallel during the migration. We implemented this feature as XML configuration file to maintain the necessary level of flexibility.
Using our solution, the customer was able to perform the migration automatically, even though the customer team running our utility was never in contact with our development team.
Feel free to contact us to complete your migration project.
