Best Practice for ReportServer and ReportServerTemp DB location

Question

Best Practice for ReportServer and ReportServerTemp DB location

Geoff A

SSChampion

Points: 11511
More actions
May 11, 2012 at 12:52 pm

#392243

i want to build an SSRS server that will serve reports for several different databases.
in the past, where ever the prod database lived is where i would create the report server databases and SSRS would run on a seperate server.
considering performance, is it better to have the report server databases on the same instance where the prod databases live OR would it be better to run SSRS and a database engine on one server and the only databases on the SSRS server would be ReportServer and ReportServerTemp?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Your question is not easily answered.

There are a number of factors such as server sizing and resource utilization that come to mind first. If your server is large enough to handle the loads or your user base is small enough to not tax the server then a combined server might be ok. Licensing is also a factor, if you install any part of SQL on another server you have to license THAT server as well. So if you break up reporting services (regardless of RS DB location) from the server that contains the underlying data you are paying for two servers.

If your loading is high it may make sense to break out the SSRS server from the rest of the data. This leads to the question of whether you are going to leave the RS DBs on the existing DB server, this can go either way, in my mind likely related to load on the server. You also have to keep in mind whether you might do a scale out or scale up deployment if your SSRS needs grow. If you are looking at a scale out (use Enterprise edition) then those DBs will be in one place for all SSRS servers in that deployment. If you are doing a scale up then you have one set for each SSRS server. For me where those are placed always comes back to server sizing and load factors.

So short answer, I can't give you a solid answer for you.

What I can say is that if the SSRS servers are sized properly I like having them have an instance of SQL locally that handles the SSRS related DBs. This generally precludes an SSRS scale-out deployment but you can simulate much of that by religeously always applying the same changes to each SSRS server in a deployment. Another reason I like this is each SSRS server gets its own RS Temp Db. Enterprise edition is expensive so it is used less and things like a scale-out deployment doesn't usually rate high enough to warrant the extra cost.

I'm sorry if that didn't really answer your question..

CEWII

Geoff A SSChampion Points: 11511 More actions · Answer 2

thanks for the reply.

just reading your response made me consider things I had not previously considered.

in my case, i guess my biggest worry is this senario;

Server1 has a single 150 Gb data base.

Server2 runs SSRS.

(we have 5 setups that are like that)

does the location of ReportServer and ReportServerTemp run better on Server1 or Server2 if both servers are identical?

I know it is a loaded question. but i am looking for best practice.

at my current location, we have always put ReportServer and ReportServerTemp on Server1. We did this only to avoid running database services on Server2. Performance has always been fine.

but now consolidation is king. and the business wants a single SSRS server to service multiple Server1 setups.

So, a single SSRS with 1 instance means 1 copy of ReportServer and ReportServerTemp. That is why i was considering running those databases on the actual SSRS server. I did not want to have to pick one of the 5 Server1 servers to run the ReportServer and ReportServerTemp databases. Likewise, i do not want to run 5 instances of SSRS with 5 copies of ReportServer and ReportServerTemp databases.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I like not having database services running on Server 2 however its not real hard to limit the attack surface of a SQL server that you are only allowing local access to.

Taking your scenario as if it were mine.

I would likely put in 2 SSRS servers with a local SQL running to manage SSRS. Then I would RELIGEOUSLY apply the same changes to each.

In this scenario I would have a clear separation between SSRS and the servers that provide data for SSRS. This also cuts down on outage scenarios because the server hosting the SSRS databases would need to be up even if that server was not being used to return data.

But like many things, it depends.

CEWII