Increase Database Performance By Setting Up Replication to a Data Warehouse

Does your database retain all your historical data as well as operational data that is needed day to day? If so, why not move the historical data to a new storage space designed for better analytics and reporting while continuing to keep your operational database lean and fast?   

This article will walk you through a solution using real-time change data capture to continuously stream all inserts and updates to a data warehouse while ignoring deletes. This allows the operational database to be purged down to only contain the data that is needed for daily operations. All historical data is compiled over time through replication to the warehouse so it is never lost. By separating the operational and historical data all reporting and analytics can work against the warehouse rather than providing more processing by the operational database.

Prerequisites

– SymmetricDS is installed and connected to both the operational database and warehouse database

– All tables to be replicated to the warehouse have been configured in SymmetricDS

– Purge processes are in place to remove data from the operational database outside of SymmetricDS to reduce it in size regularly

SymmetricDS Configuration 

In order to set up SymmetricDS to replicate only inserts and updates to the warehouse node, the deletes simply need to be configured to be turned off.  By navigating to the configure -> table triggers screen, all table trigger configurations used to replicate to the warehouse can be selected in bulk. 

Once all the table triggers are selected, hit the “Edit” button to make adjustments to all these configured triggers at the same time. On the bulk edit screen, the “Sync on Delete” checkbox needs to be turned off so that deletes are no longer replicated to the warehouse and all historical data can be retained. Once unchecked, hit “Save” and you are configured to no longer send deletes to the warehouse node.

After saving these changes the triggers will automatically be reconstructed and you are set to send only inserts and deletes to your warehouse. The foundation is now in place to begin purging old data out of your operational database without fear of losing it. Instead, all data is now retained in a warehouse designed for large data sets to be accessed through various reporting and analytics tools.