Sql-server – Where to put ReportServer and ReportServerTempDB databases

sql-server-2008-r2ssrs

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!

Best Answer

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.

Related Solutions

Sql-server – ReportServerTempDB in AlwaysOn Availability group

Microsoft describes it's support for Availability Groups with SSRS in BOL. In particular is the section titled "Report Server Databases and Availability Groups." In my experience, having ReportServer and ReportServerTempDb be part of an AG works fine in most cases, but there is some peculiar behavior with subscriptions and failover--which is described in the BOL article.

Having independent ReportServerTempDb databases on each server, but not participating in the AG is not something I would try--but it would likely be problematic in it's own ways. It would also not be a supported configuration.

You should look at this answer to see why your transaction log is growing. I suspect it is due to an open transaction in the ReportServerTempDb. On a number of occasions, I have seen the CleanBatch process get hung, and create a scenario where it has an open transaction but is performing no work. The net result is that your transaction log gets huge. If you see the same behavior, then simply kill the spid with the open transaction, and things should return to normal.

SSRS – Moving Report Server Databases to Another Drive

There will be minimal downtime, so plan this in a maintenance window:

Don't use detach/attach!

Have verified backups!

Use Alter database...modify file... (do below for mdf and ldf files)

use master;
alter database dbname 
modify file (
    name = 'logicalName', 
    filename = 'E:\RS\db_name.mdf'); --> This is the new file location

Now offline the database:

alter database db_name set offline with rollback immediate;

Physically copy the mdf and ldf files to the new location.
Bring the database online:
```
alter database db_name set online;
```

If you script out and test out on a dummy database, you will see how much time it takes and will build your confidence.

Best Answer

Related Solutions

Sql-server – ReportServerTempDB in AlwaysOn Availability group

SSRS – Moving Report Server Databases to Another Drive

Related Question