Architecture for reports

Question

We need to build reports in our system. We can't build them online on tables, because of 2 reasons:

1. Complicated logic
2. We want to move reports to another system (just publish some facts them and they build reports)

So, we need to collect facts about user actions. Today we have some job, that analyze CDC changes. But this approach has some problems:

1. CDC change is not a fact we need, we must do some operations
2. Slow processing of CDC changes

But it helps us to collect all actions (even made by sql update script) and it's async (has no influence on user operation perfomance).

Another way - collect facts on Business Logic layer, and send them to another system or save to table. But it's synchronously operation, and difficult to collect facts made by sql scripts.

So, what is the optimal way to do it? Requirements:

1. Collect all changes/facts (even made by sql scripts)
2. Has little influence on user operations perfomance

Answer 1

I assume you don't want to report from Transaction Server. So you may need to setup a reporting server via replication ( Merge or Transaction Replication).

Replication, Change Tracking