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

Answer 2

You can use change data capture (CDC) or a logging framework or use both.

You can embed your custom logging rules inside your business Logic layer & log them separately via your logging framework in your application (to a text file, separate database tables etc), using Log4net or any other framework.

Ideally, if this log is written to a separate database, you can generate all sorts of customized reports. If you need to record every database activity, you can setup Change Data Capture or use SQL Triggers to capture certain events only (This would require the most work in setup & testing). Some useful links I found are

Auditing triggers in SQL Server Databases

Log Records in SQL Server in Audit tables