On our SQL Server, we have a database for each of our web apps. For reports, we use Reporting Services and all report data (including report parameters) come from stored procedures.
The stored procedures are in the same database as the data in the report. So, for example, the procs that serve the Stock reports are in the Stock database. Some reports show information from more than one database and then the proc will be in one of those source databases. The report parameters get their data from procs in an Enterprise database that has data like stores, employees etc.
This means that all reports have at least a connection to the Enterprise database and another connection to another database — and sometimes more than that.
My question is: is there a benefit of moving the reporting procs into a separate "Reports" database. I know the benefits of moving reports onto another server and I'm not talking about that — this would be on the same server.
Things that might affect this are:
- Does having more than one database connection for a report, affect the speed of the report?
- Would having the reporting proc in a separate database from the data, prevent us from using indexed views?
- Have you found it easier / harder is administer you reports in a separate database?
Please let me know what you think.
Best Answer
The answer is: yes, there is a benefit to doing it. Reports on on operational database will use a lot of resources and will interfere with the performance of the operational system. Remember that database performance is subject to mechanical constraints (disk heads moving back and forth and rotational latency as we wait for the right sector to make its appearance under the head). You have two broad options for a reporting strategy: