I have always heard that having a separate reporting database from your OLTP database is a good practice for reasons of performance.
In my particular application, we have perhaps 1,000 concurrent users. Each user is continuously adding new records to tables.
There are very few updates to records (almost none), and very few reads. When updates or reads occur, they occur to single
records belonging to that user, executed by that user. In addition we have perhaps 10 admin people that periodically run
reports on big segments of the data (perhaps the last 3 months of records).
We have thought about using replication to copy data to another computer, and then have the reports run on that computer.
I have several questions concerning this:
1) What performance implications are there in this strategy (as opposed to say, adding more CPUs to the main machine)?
Could you give a brief description of how performance is enhanced (if it is) by separating OLTP from reporting.
2) Do you have any real world test cases or scenarios where this was done? What were the effects? What were some of
the unintended consequences or negative effects of the move?