Where to put ReportServer and ReportServerTempDB databases? I have a SQL Server 2008 R2 database server that hosts a data warehouse database.  On this server is also installed SQL Server Reporting Services, and its two databases ReportServer and ReportServerTempDB.

For performance reasons I want to separate the data warehouse database and the Reporting Services.  I have another server that is available to install SSRS onto.  Both servers are virtual and so their specifications are easily changed, or other servers could be 'created' if necessary.

What I can't get my head around is where the best place would be to host the ReportServer and ReportServerTempDB.  I can see three options:


leave them with the data warehouse database
put them on the server that will have Reporting Services installed on it
create another server specifically to host those two databases.


With option 1 I worry that separating the Reporting Services from its databases will either impact the data warehouse server, or will impact the reports.  With option 2 I worry that installing the database engine on the server will impact the Reporting Services server performance, and with option 3 I worry that I'm wasting resources creating a server that will hardly do anything, and also impacting the reports by separating the Reporting Services from its database.

I think the key piece of information I need is to know which part of Reporting Services is the most resource intensive.  If it's the Reporting Services application then I can leave the databases with the data warehouse.  If it's the databases that are resource intensive then I need to either put them with the Reporting Services application or create a new server.

Can anybody shed some light please?

Thanks!
SSRS Planning a Deployment would be a good article to read through.

You can read specifically on the SSRS databases here. As it states:


  A report server is a stateless server that uses the SQL Server
  Database Engine to store metadata and object definitions. A Reporting
  Services installation uses two databases to separate persistent data
  storage from temporary storage requirements. The databases are created
  together and bound by name. By default, the database names are
  reportserver and reportservertempdb, respectively.


You general, with large reporting requirements, will run SSRS databases on one server (shared SQL Server instance) and then the SSRS components on another. Keep in mind though how licensing works, you will have two licensed SQL Server servers with this type of setup. I normally see the report server databases reside on the same server as a data warehouse. I have not seen any issue with the SSRS databases being on a shared instance with the data warehouse. As it states in the article they are only storing information for SSRS (execution log, schedules, report definitions, etc.).

The "meat" of what SSRS does resides with the SSRS component services. SSRS processes the data the report is requesting and then also works out rendering it, all of this is resource intensive if there is a high load. It will depend on what your specific needs are if it justifies a standalone server. I generally will see SSRS installed on the web server with the application that uses the reports.

To determine if I need a standalone report server I would probably consider things like number of reports (many small or many large), the frequency (internal report server for monthly reporting or serving up user reports for external web application). You may also look at the report design or standard used by the developers, if the reports do a high amount of data processing on the report side, versus letting the database engine do it.