Modifying their structure is generally frowned upon

Replication is more than likely out and I'd throw Sync out before that. (from real life high transacitonal tests on Sync Framework)

If 3-4 hours is your data latency exceptance, log shipping will probably be your bests bet here on a read-only copy. But how much change is happening in the log? find that out be monitoring it to see how quickly and how much you need to push across.

If you can't go to Mirroring or upgrade to 2012 enterprise, that is out although that would be a sound strategy if you can go Enterprise if not on it.

SSIS isn't meant to just dump data over but it can do it. You're looking at far too much in the terms of lookup transformations though and the task would be expensive in time and resources. Although, like I said, it can do it.

Really, there will be a distinct narrowing of choices based on answering a few questions

• Data latency acceptance
• Amount of data change in a given minute,hour and day Connectivity to the secondary
• Read requirements on the secondary instance
• Up-time of the secondary instance
• Alterations to existing schema and all objects
• Security

Assuming that there was a serious failure which causes you to need to 'reconstitute' the missing type 2 changes, you may be able to use your source database data and log backups. If you have preserved these backups you can do something like this process outline:

1. Restore a copy of the source database to a point just prior to when you experienced the failure. Name the database [dbnameEmergency].
2. Restore the logs incrementally to [dbnameEmergency], such as 1 minute, 5 minutes, 30 minutes, according to how finely you want to process the deltas in order to reduce the damage.
3. Reading and comparing the incremental restores will require either creating snapshots, if you have Enterprise edition, or restoring the log to STANDBY mode so as to read the data.
4. Compare the latest readable changes in [dbnameEmergency] against the state of the [dbnameReplica] (which is the database you are trying to set straight) and write code to apply the changes you are able to infer to [dbnameReplica].

Note: If your database is using a rowversion (or timestamp) column then finding the changed rows will be simpler.

This is not a perfect process and will doubtless miss some details. But it would give you an approach to help you patch up the damage.

Having said that: It is up to you to decide whether this process is worth the effort.