Thanks Grant

This is a business requirement to keep this data in warehouse dB. I’m not aware of anything behind the scenes.

500 tables I mentioned is 1/4th of the count of the whole tables on that dB. Initial load can be done as one time load and incremental load for changed/new...

Copy all changes to the table, not few columns. Thanks

Oracle box has 99.99% availability. Thanks

Hi Mike, we are planning to implement what you have mentioned. Adding few audit columns for insert and update and use those columns for ssis to extract/load the change data....

Out warehouse is is implemented on oracle. Not sure that makes a difference. Thanks!

That is right. Using the audit columns to help identify data inserted/modified. Data load is planning to do daily using ssis. Thanks!

Do you think transactional replication is a good choice for 500 tables. Any performance impact on one vs the other?

Thanks!

Thanks Ron!

Thanks Jeff.

Primary function of these audit columns is to help load data from oltp to data warehouse using insert/update audit columns using ssis. Thought about cdc, but they find...

Thanks for positive response Jeff.

Since the audit triggers are going to implement for multiple tables(over 500), is there a negative impact on adding as a new trigger vs modifying existing....

Just use the above steps as a starting point.

Did you check if the agent service account on dr server has read/write access on the shares(back up and copy directories). Check share security properties. Just try to manually access...

Thanks Sue!