I would like to know if i have a web with a huge Database and throw expensive (in time)reports , the best way to do this is with one database for the web and a replicated one for reports, or only one for both, i'm worried that users can throw reports for 5 or more years because they need that information and the web crashes because of this.
2 Answers
Neither nore - time to make a second database that is optimized for reporting. This is standard operation procedure and is called a "Data Warehouse".
This should possibly also work on separate hardware, but this is "arguable" as long as the server is good enough.
Transactional databases (OLTP - highly normalized) and reporting databases (OLAP - star schemata, different design characteristics) do not mix well for larger loads.
replicate/copy or do something!
Yes! because of the nature of OLTP, locking and resources you do not want to be running report queries against a production live copy of your db.
Setup replication to another server, or something to get copies of your prod data onto another box. The large scale measured & proper approach is DW's, ETL's star schema ect.
For a small system its baby steps though.
answered May 19, 2010 at 14:31
