We use SQL reports in a .NET web application, but some of our reports are heavy and involve more table join and columns.
As you know we cannot index all the involved columns (performance problem).
These reports impact web application performance. Now we are running reports against the same database that is serving web application.
What are some solutions to have reports on a separate database?
It's pretty standard practice to have a data warehouse separate from your application database to run reports off of. This keeps your app from slowing down when generating reports and allows you to keep more data and in a more suitable (denormalized if necessary) format for running reports.
It's analogous in principle to Command Query Separation/CQRS in programming. So your application database is optimized for recording and high transaction volumes (fewer indices, smaller index sizes, normalized schema, sharding, and other optimizations that make sense in an OLTP scenario), whereas your data warehouse is an OLAP database optimized for reading and running reports, and will probably use a star or snowflake schema or a denormalized schema, make use of aggregates, etc.
Why not setup a version of the database on another server, simply have the two replicate every 12 hours, and you can then run your reports without killing the live server.
What about creating views for the reports you need instead of rerunning all the joins?
