One of our clients, a large financial software company, wanted to migrate their billing solution from Oracle to SQL Server. They needed to initiate this project because the migration was required by one of their strategic partners. The customer decided to implement SQL Server Integration Services (SSIS) for this migration.
Additionally, they needed to do the data transfer from Oracle to SQL Server. Because of the complexity of the project, the client sought software developers that would build an automated migration solution and would provide their remote team with detailed instructions on how to use it.
To make this happen, ABCloudz specialists applied their technical expertise to develop a command prompt migration utility that met the customer’s needs. The solution was accompanied by extensive documentation for their remote team’s use later. The most significant benefit of the solution was that it provided the client with the capability to migrate all data within the designated outage window by themselves using their in-house team. If you’d like to know more about how our database migration with SSIS works, make sure to check the video below.
The key component of our solution was the SQL Server Integration Services (SSIS). We used it to automate data migration. SSIS allows the customer to extract data from the source Oracle database and save it as SSIS packages. The client can also use PowerShell to automate the execution of packages. This gives them an automatic mode feature for loading the data to the target SQL Server database.
High-level Oracle to SQL Server migration: a step-by-step approach
Here is the step-by-step methodology we employed for building the database migration foundry for our client:
1. The first step involved deploying the target SQL Server database on the newly-provisioned production environment of SQL Server. We also provided the client with a backup file for restoration. It allowed the customer to create an empty database containing the important schema structure.
2. For the second step, we reviewed the connectivity to the source and target database. We applied Oracle SQL Developer and SQL Server Management Studio for this purpose.
3. The third step involved using our command prompt migration utility. We applied it to migrate the data from all tables hosted in the source Oracle database. The user needed to choose the SSIS packages they want to execute in the command prompt window. Our technology specialists provided high customizability functionalities for this process. Specifically, the user could migrate all tables or choose a single table for migration. Then, we provided a migration iteration label. It was displayed in the command prompt and was required for future data validation.
4. Migration validation was the fourth step in our high-level Oracle to SQL Server migration. At that point, we reviewed the migration logs to make sure that no errors had occurred during the previous stages. We also reviewed all migrated tables in source and target databases to compare their row counts. After that, our client used Oracle SQL Developer and SQL Server Management Studio to verify that query result sets in the migrated database returned the right data. By doing this, they ensured that there were no critical data inconsistencies resulting from the migration.
5. Once the migration process was completed, SQL Server Management Studio was used to provide the client with a full database backup.
Additional features of the solution
We provided the client with an efficient solution for database migration automation. To improve their experience with the solution, we attached detailed implementation instructions for reference. These instructions included screenshots covering the most common migration errors. The client could use them to easily handle issues with executing SSIS packages. The instructions also included a detailed explanation of database recovery in case of its possible corruption.
Our solution enabled the client to define the number of SSIS packages executed in parallel during the migration. Our technology experts provided the client with a high level of flexibility and this control feature was delivered as an XML configuration file.
For this product, our tech experts enabled the customer to use SSIS for automated migration from Oracle to SQL Server. The client was satisfied with the clarity and the convenience of the solution. Since they began running our utility, the client’s team hasn’t needed to contact our development team for any issues.
Are you interested in efficient database migration automation? Make sure to contact ABCloudz to discuss your needs and find out how we can facilitate your plans.
