Tags: Always On Availability Groups, ETL, Informatica, Jaspersoft, Talend
Our customer needed to reduce the licensing costs of their Oracle database. We came up with a comprehensive solution by migrating their database to SQL Server platform. We also remediated all affected application components. As a result, our customer benefited from obtaining higher value SQL Server platform with enhanced scalability and high availability approaches.
Customer’s problem
Our customer has been hosting their application in AWS cloud. They wanted to migrate from Oracle database to SQL Server, but the rest of the current environment should be left in place, except for ETL solution. So, in addition to database migration, we had to upgrade all the affected components, namely to remediate the application and reports calls.
Here is what we ended up doing for our customer:
- Database migration from Oracle to SQL Server running in AWS RDS Cluster with configured Always On Availability Groups;
- ETL layer migration from Informatica to Talend;
- User interface components remediation by converting Oracle PL/SQL calls to SQL Server T-SQL calls;
- Conversion of Jaspersoft reporting tool calls to SQL Server calls.
Prior to the actual migration, we had to prepare the development environment and configure source control for all migrated objects on GitHub.
Moreover, we eliminated possible architectural risks by configuring the maximum degree of parallelism and installing the latest SQL Server service pack.
Schema conversion
The customer designed the existing application in such a way that each client had a dedicated schema in Oracle database. We decided to optimize this architecture and implement one database per client model in order to have better control and scalability. The major challenge here was a cross-schema Foreign Key dependencies between one global schema and the customers’ schemas. To keep this dependency in the new architecture we placed a copy of global schema into each new database containing client schema.
For schema conversion task we used SQL Server Migration Assistant that allowed us to convert most of the code in automatic mode. However, we faced a number of cases that needed manual conversion. One of the major challenges that we solved was data types conversion. For example, Oracle “number” data type is converted to a “float” by default for maximum flexibility. In some cases it was better to convert “number” to “bigint” data type where such a high level of precision was needless. Additionally, we reduced character string values to 4000 characters to match the validation logic that does not allow greater lengths.
We finalized that migration step by utilizing Database Compare Suite to validate schema conversion.
Data migration
Using ETL scripting, we migrated the customer datasets from existing Oracle database into metaschemas inside each new SQL Server customer databases. We had to migrate about 200 GB of data distributed across global and customer schemas. Using OraMigrator tool we customized the migration process by splitting the tables into 4 groups and then ran them in parallel to reduce the migration time. To protect the data during the migration, we utilized Zmanda cloud backup tool. We configured that tool to perform SQL Server backups directly in the AWS cloud.
Code conversion
Once the database migration was completed, we had to reconnect all currently existing components to the new SQL Server database. So we manually converted the PL/SQL queries embedded in Java app and Jaspersoft reporting tool to their T-SQL equivalents.
Our customer was using Informatica solution to process ETL workflow. Because they wanted to change ETL tool to Talend, we had to execute a particular sequence of steps to convert the workflow. First, we had to convert business logic. Informatica and Talend have different logic, so that was not the direct conversion, but rather the recreation of workflow functionality.
After that, we recreated the built in Informatica file management and audit features that were not natively available in Talend. Moreover, we improved the original workflow design to better handle future growth. Finally, we configured the shell scripts to launch the workflow.
High availability configuration
The customer arranged his database hardware in a redundant pair using Oracle Data Guard which keeps the standby database up to date for a possible failover. So, we configured Always On Availability Groups to recreate the Data Guard approach in the new SQL Server database. This includes a primary replica (read-write) and a secondary replica that serves as a hot standby. We used a 2-phase commit process for synchronous replication.
In the original system, our customer configured Amazon Elastic IP addresses to protect the application from possible failover and prevent the downtime. We replaced Elastic IP with a Microsoft based Virtual IP, which effectively serves the same purpose.
We designed database infrastructure hosted in AWS to provide easy vertical growth as performance needs expand through elastic resources. The Availability Groups design will allow horizontal growth by adding more replicas. Now the AWS model allows on the fly storage adjustments in case of need.
Conclusion
Once we completed all major steps, we validated the migration using Database Compare Suite. That way we compared data values by a combination of checksum comparisons, table by table comparisons and more detailed row level analysis to provide a quality report. We also performed the unit testing of the ETL workflows and all converted code objects.
Moreover, we prepared documentation with detailed description of all migrated components and best practices on operating the new SQL Server environment.
As a result, the customer benefited from obtaining:
- turnkey migration solution;
- scalable architecture designed for future workloads growing;
- fine-tuned Always On Availability Groups adapted for expanding performance needs.
