Are you looking for Logical Replication for Database Migration ?

What are we trying to Solve ?

Do you want to upgrade your mission critical complex mammoth RDBMS in house or in cloud on AWS EC2 with a solid rollback strategy ? Do you want to migrate the RDBMS from in house to cloud with a fallback option ? Example you want to upgrade from SQL Server 2012 to 2019 , there could be different strategies to reach the end state. Choosing an upgrade method isn’t easy one. It depends on so many variables in your environment that you have to step back and approach it in a holistic way. It has to be a combined decision of both the business and the technical team. On a high level upgrade steps will be to prepare to upgrade , test the upgrade process , test the upgraded QA/UAT/E2E database , prepare and preserve the production database , upgrade the production database , tune and adjust the new production database .

Upgrading a database irrespective of flavor of RDBMS can be a large source of business unit inconvenience , resulting in downtime , functionality/feature changes , compatibility difference , and unknowns ( how the overall workload will perform on an upgraded database version). Upgrading , however, becomes critical not just because of new possibilities and features that come with the upgraded versions but because the lower versions won’t be supported for a long time. As a result , business units find themselves in a very vulnerable position with their data and mission-critical applications which are bread earners for the organization hence can’t afford to have extended downtime or performance degradation post migration impacting customer experience .

There are various options to upgrade your database and one of them is In-place upgrade whereby all actions are happening on the same hardware and just upgrading the database version. Any issue during upgrade needs to be resolved then and there itself which might end up with extended upgrade time .The rollback process for an in-place upgrade is to reinstall lower version , restore old database which could lead to substantial downtime. Also you need to apply transactions which went post opening the traffic to customers hence becomes too risky in case you have to rollback to a lower version.

To cover risk you will be inclined towards a side-by-side upgrade whereby you need to install a higher version of SQL Server on a different machine , restore the DB from the lower version and run all testing before pointing real customer traffic . If you have a robust performance suite then you want to incline to choose this option. In case of any issues the rollback strategy will be to redirect traffic to a lower version. Although this method takes care of any unknowns to execute the upgrade process as compared to in-place upgrade . The rollback still becomes trivial , painful and complex post the higher version starts taking real traffic and you realize after a few hours the higher version doesn’t have similar performance and customers are observing degradation , at that moment you can’t rollback back to the lower version till all transactions are applied back to the old primary leading to extended downtime.

You can test and retest on higher versions but you can’t cover all corner cases.If there isn’t 100% assurance how the higher version will interact while taking real customer traffic then its a must to have the upgrade process with a solid fallback option. You might want to set up a higher version with a method to move real time data from lower to higher version when the lower version is the primary database plus the ability to set up reverse replication once the higher version becomes primary . This strategy provides a solid rollback method in case required and provides the capability to rollback after a few hours and even after a few days . The strategy requires a logical replication set up to move around the data between lower to higher version and vice versa. There is a cost involved with the strategy but if your application can’t afford upgrade downtime , degraded performance post upgrade then this strategy takes care most of the upgrade challenges , nervousness and provides flexibility to move traffic back to lower version with minimal downtime.

The blogs guide on the various criterias to be tested before determining which logical replication product will work for you before you plan to consume the right replication methodology to move the data .

Replication method & Key Criteria

The criteria list might vary as per business requirement but an attempt was made to list down key criterias which will cover the majority of the use cases . Determine basic and must have requirements to set up logical replication. Below is a list of key criteria .

• Support replication across various version of RDBMS
• Minimal to negligible impact on source database
• Ability to start and stop with a timestamp with continuous reverse replication capabilities
• Provide automation on DB schema evolution thereby moving DDL definition from source to target without human intervention
• Ability to provide data validation and repair option in case of data discrepancy
• Support currently used data types with minimal latency during logical replication
• UI functionality to monitor and alerting is a plus
• The product should be easy to install with minimal configuration
• Failover recovery in case of unavailability of source or target database destination
• Scalable , ability to consume the delta data produced during the peak business cycles and tune for high data volumes
• Cost effective and widely used

Available Products & Success Metrics

As you note down the criteria’s next step would be to determine how you will test with what will be the key criteria and success metrics of each criteria. Sequence out your testing starting with functionality tests and later ramping up to support your business case . Look out for available products for logical replication in the market.

In our proof of concept we chose SQL Server as RDBMS and tested to replicate the data from lower to higher version of SQL Server with below key criterias

The product comparison was done on above key criterias among AWS DMS Service , Oracle Goldengate and HVR software

Conclusion

Although there will be many others criteria based on your business needs to determine which products you should choose but as per the testing on SQL Server 2012 ( source ) to SQL Server 2019 ( target ) and using logical replication to keep target in sync, it was found HVR replication software came to the top as it provides one stop solution for data replication , validation , repair , monitoring , alerting over email and slack with UI capabilities . The source system had minimal impact while capturing data changes ( a big plus ) as the product doesn’t read CDC tables and instead reads transaction logs to build the delta changes. Also the product does provide rich features like auto DDL deployment , flexibility to validate and repair data subset.

Some of the DDL like deployment of views , functions , procedures propagation isn’t supported on many of the logical replication methods and needs to be taken care of manually. Although already existing objects will be migrated if the backup and restore process was used to initialize your target database. As of Nov 2020 , some of the DDL like rename DDL support is currently been worked on and is expected to be soon available on HVR

Author : Rajesh Saluja

Reviewer : Tushar Thakker

The blog content is a team effort and I would like to Thank the reviewer and several other cross functional team members on their feedback and recommendations.