Reporting solutions

Question

I am soliciting for suggestions on reporting solutions

We develop a lot of in house projects(.net and sql server). For larger database we use business objects and build universes for reporting so that analysts or report writers can build reports and developers dont need to be involved.

Many of our projects house important data, but are not large enough to warrent universes and data warehouses being built on them. We still need to build reports off of this data but we don't want to report off the live databases as that could affect performance of the applications. For some of our projects, we do nightly backups/restores, effectively duplicating the database and then use the copy as a reporting database. Not having a lot of reporting experience, I'm wondering what other solutions people have implemented.

Answer 1

In situations like this, we've set up transactional replication from the OLTP database to a secondary database that is used for reporting purposes.

Answer 2

If you are running 2008 you can use the resource governor to limit cpu and memory usage on your production database server by your reporting users. Best scenario is to have a dedicated reporting server and db, but this can work.