SQL Server - separate database for reports?

Question

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.

Answer 1

I think it much depends on the kind of SP you're running. If they are heavy and could effect other things running on the database server I'd move them. Otherwise I'd try and keep the close to the database they're actually reporting on, if find that much easier to maintain and keep track of. Just having the report close to the actual database could also affect performance but if your on a standard setup and not moving enormous amount of data that would be a tiny difference I guess.

I've also found this article useful.

Answer 2

I'd recommend against moving the stored procedures to another database for several reasons. From a development perspective you have to reproduce two databases each time you want to make alterations. As a consequence you will now how to synchronize the schema from the "data" database and the stored procedures from the second one with the production versions. In regards to disaster recovery and backup/restore, you now have to concern yourself with restoration of 2 database just to get your system up and running.

When testing you also have added complexities. You'll have more points of failure in respect to permissions, versions, etc. Now if you have more than 1 person working on different initiatives on the database(s) you have more spend more time coordinating efforts. Now imagine its 3 am, the system is down and you have to hunt through the permissions on all databases, and have to ensure that no one has left a function or procedure on the wrong database during development.

Answer 3

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:

1. Replicate your database onto another server and move the reporting sprocs onto it. Reports are run off the replicated server. This is the least effort and can re-use your existing reports and stored procedures.
2. Build a Data Warehouse that consolidates the data from your production systems and transforms it into a form that is much friendlier for reporting. If you have a lot of ad-hoc statistical reporting that could be done acceptably from a snapshot as of 'close of business yesterday' a data warehouse might be the better approach.

Answer 4

I'd recommend you use two databases.

Deriving reports from a 'live' database causes performance problems.

Also, since the reporting database is primarily for searches, you could customize the indexes here for better performance. (The live database would have inserts which would be affected adversely by certain indexes)

Answer 5

My questions are: When you move the RS and/or the DW to another server do you have an existing Database Engine on this(these) new server(s)? or are you using the original database engine?

• So do you have to have separate database engine for all SQL servers?

Thanks, - Dom