My company would like to make use of Change Data Capture to replace Interchange of Interface Files between Upstream System and Downstream Systems. Upstream System runs in Oracle Database and contains superset of data while Downstream Systems run in MySQL Database and contains subset of data which are not totally mutual exclusive. We decided to use CDC because we would like to enjoy
- Data transfer by delta instead of full-set
- Automatic data synchronization
- Automatic re-send if data transfer interrupted
However, compared with interface file, we found the following drawback of CDC
- Too complex from Architecture's point of view
- High security control demand in both ends and the middle network
- Complex data management as different recipients need different sets of data
- Create single point of failure
- Not transparent in the data transferred, compared with plain text file
- Difficult to control effective time of data in downstream system if synchronization is real-time
- Considerably high cost than File Transfer
How can we overcome the above disadvantages?