Data replication
and data migration
software

​​​​​​​Goal:
On-prem SQL Server to AWS Aurora PostgreSQL migration

Challenges:

Database structures in Aurora and SQL Server are not similar – such migration requires complex customized data transformations. Since fintech is extremely sensitive about data integrity – in this scenario deep data validation and comparison between source and target is an absolute must with 100% match. The requirements for downtime (under 30 minutes) and for data quality (a 100% match) were extremely stringent.

Why: This migration was needed to optimize costs related to current setup (on-prem) and reduction in unnecessary licensing and integration with cloud infrastructure with additional technological benefits

Repstance approach:

• Two distributed EC2 instances (m5.4xlarge) were used
• Over 70 transformation rules affecting 6,000 objects were developed
• Initial data migration and ongoing replication was set up
• Full data comparison to validate 20 TB of data was executed in parallel with ongoing data replication
• Incremental data comparison was running daily only to validate the data that was changed since the last validation point (average daily change size – 150 GB)
• The last incremental data comparison was executed as part of the cutover procedure to ensure full data integrity between both source and target databases

Result:

The switch happened without service interruption. The company moved away from commercial licenses, got rid of legacy code and now runs on an upgraded, more stable codebase.

​​​​​​​Goal:  

On-prem SQL Server 2016 to SQL Server – ongoing replication

Challenges:

Too many tables (60,000) with ongoing DDL and DML operations, an uneven load and a “15 mins lag” requirement make this extremely hard to handle.

Why: Need to unload the production system by moving BI/reporting functionality to the mirrored instance without any interruptions 

Repstance approach:

• On-prem version of Repstance instance was used. Initial data migration was initiated with native MSSQL backup/recovery.
• Repstance was configured to replicate data from the point when backup was taken.
• After that data was validated both on source and target with 10% depth (for quick validation).
• System monitoring was configured to flag any incidents related to replication errors or lag exceeding 15 mins.

Result:

The BI and reporting load now runs on the mirrored instance, so production is relieved of it. Replication lag stayed under the required 15 minutes.

Goal:

Implement zero downtime upgrade for SQL Server databases 

Challenges:

The biggest challenge is related to a very small change window (under 3 mins) because product is used in mission critical scenarios by end users.  The second challenge is related to complex transformation and data manipulation because of the differences in database versions across source and target databases. 

Why: Client needs to roll out timely and reliable updates/patches/etc for their end users to keep users constantly up to date with the latest changes in the product

Repstance approach:

• Customer uses Repstance as a part of the deployment process to replicate data between databases running different versions of the product and implementing bespoke data processing logic
• The deployment of Repstance instance is fully automated with Terraform and Repstance REST API
• All databases are fully synchronized and the end users seamlessly switch to the new version in under 3 mins

Result:

Around 60 databases are upgraded this way, twice a year, and the under-3-minute requirement was met in every migration.

​​​​​​​Goal:

Initial data load and ongoing data replication from Oracle to S3 and Snowflake 

Challenges:

The large number of partitions, with new partitions constantly added, made the data delivery speed requirements hard to meet.  Long running transactions are typical for this customer’s database and this made the task even more complex.

Why: Data needs to be delivered to end user’s partners either to S3 or Snowflake to address specific business needs (daily data flow).  This data is used for decision making, reporting, and deep data analysis. 

Repstance approach:

• Repstance used one capture to extract data from Oracle and two applies to propagate collected data to S3 and Snowflake
• Replication was configured to capture data changes from the standby REDO logs to avoid impact on the Source database
• Based on the defined filtering and transformation rules the data was accurately distributed to required destination
• Speed of DDL changes processing allowed to overcome the challenge of added partitions
• Repstance processed the long transactions that are typical for this customer without performance problems, which was the main criterion for choosing a replication product

Result:

Data reaches the partners in S3 and Snowflake every day, on time, with replication lag under the required 30 minutes.

Goal:
Set up replication from Oracle 19c RAC (4 nodes) standby database to AWS Aurora PostgreSQL

Challenges:

The source database needs to have minimal possible impact during the replication

Why: The data needs to be replicated to an Aurora PostgreSQL database for business analytics and reporting

Repstance approach:

• Repstance was using direct log mining mode to extract data changes from Oracle archived REDO logs that were taken from standby instance (ASM device)
• Repstance did not need to interact with the primary source database and completely minimized the replication impact
• In accordance with the customer's security policy, the Source and Target databases must be isolated. Under these circumstances 2 Repstance instances were used, one to extract data from the Source database and another to apply the changes to the Target database. The interaction between Repstance instances was established via S3

Result:

Replication lag never exceeded the required 30 minutes: Oracle switched logs every 15 minutes, a cron job copied each new archived log to the Repstance instance every minute, and it took about 2 minutes to process the logs and apply the changes to Aurora PostgreSQL. The primary source database was not affected.

Goal:
Migrate an Oracle RAC database (4 nodes, ASM) to PostgreSQL

Challenges:

If a legacy application failed to work with PostgreSQL after the cutover, it had to keep working with Oracle, so there had to be a way back to Oracle with the current data.

Why: This migration was needed to reduce license costs.

Repstance approach:

• Code migration was done with AWS SCT plus manual conversion. Repstance migrated and replicated the data
• Repstance ran the full data migration and then ongoing replication before the cutover
• When the lag reached 0, a full data comparison was executed
• After that an incremental data comparison ran daily, to minimize the comparison time
• Replication ran in both directions (bi-directional), so legacy applications that failed to work with PostgreSQL after the cutover could still work with Oracle

Result:

The switch happened without service interruption. None of the legacy applications had to fall back to Oracle.