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?