We have a requirement to capture the incremental changes on the source database and we have decided upon using the CDC feature in SQL Server for incremental data capture.
The below points are to be kept in mind
1. Our source is SQL Server with compatibility mode 80(deprecated mode). We are trying to work with the source system users and with Microsoft to remove the deprecated mode.
2. Even if we remove the deprecated mode, we would like to avoid implementing CDC on the source in order to avoid any additional overhead on the existing source system.
Hence we are looking at taking a copy of the existing source into another database in another box/server(target) and looking to implement CDC on the target, so that the overhead on the source can be avoided. Log-shipping is one of repliocation we are looking at but as far I have understood from various sources, log shipping requires the secondary(target) database to be READ-ONLY.
Therefore we probably cannot go for log-shipping, as CDC would make schema changes to the destination/target database and hence it would NO LONGER be READ ONLY.
Can you suggest any other feasible options for us?