Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. leave them with the data warehouse database
  2. put them on the server that will have Reporting Services installed on it
  3. 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!

share|improve this question
    
This sounds like a fun experiment in which one may use Extended Events to measure the resources used by ReportServer and ReportServerTempdb –  swasheck Oct 15 '13 at 19:12
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks for that. I had already seen the 'Planning a Deployment' article, but re-reading it again I noticed a sentence that I hadn't appreciated last time: "Additionally, the footprint of a report server database might be small at first, but disk space requirements and I/O subsystem utilization can grow significantly at run time". As the data warehouse server already struggles with I/O this is making me lean towards hosting the databases on the SSRS server. –  paulH Oct 15 '13 at 16:52
    
I don't think that the SSRS DBs will put much more pressure on IO unless your reporting workload will be severe and heavy. You may want to look at tuning your storage and/or evaluating your workloads on the DW to see where you can squeeze some performance out of the system. –  swasheck Oct 15 '13 at 19:32
    
@paulH Just moving those two database off the server that already has I/O issues is not really going to solve the I/O problem. Just keep that in mind. I seriously doubt they are the single culprit to your I/O problem. –  Shawn Melton Oct 16 '13 at 4:29
add comment

The ReportServer database generally does not grow much (keep an eye on the size of the ExecutionLog though), it's the ReportServer-service that can eat up your resources. I would put in on your data warehouse server, you can put it on a dedicated instance if you like.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.