Reporting System Architecture

Question

We have built enterprise/LOB software that, for example, manages orders, customers, inventory, etc. in .NET 3.5 and SQL Server 2008.

We do all kinds of reporting such as

1. traditional reports (SQL queries)
2. dashboards (with charts, lists, etc.)
3. alerts (e.g. if an order is cancelled, email a supervisor)

Right now, we are querying our operations database directly. We are at times having performance problems, and I wonder if this could be solved by either optimizing the way we query the database or something more involved such as using SQL Report Server or replicating the DB on another server and querying against that.

Could you guys lend any suggestions? Or any resources I can read?

Your help is greatly appreciated.

Thank you.

Answer 1

First of all using Reporting Services won't help you much, because you'll end up executing the same queries against the same database, only the rendering engine will change, it can be of some help via caching the reports though. Whether that will solve your problem depends on your load and where your problem is (inefficient queries will still be slow.)

Optimizing the queries is the first I would attempt, as it never hurts and can only be a good exercise.

If that is not enough I would consider either a read only database replica for reporting, or building an OLAP cube if your reporting can benefit from the data warehouse approach. For this last to work you have to have Analysis Services installed.

Answer 2

SSRS can actually help because it has built-in support to cache results and reports. See Report Caching in Reporting Services.