January 11, 2018 at 8:54 am
I haven't installed SSRS before, but I have installed SQL Server plenty of times (and skipped the SSRS pieces). But now, we're looking at potentially using SSRS, and going through the meetings for a preferred installation approach.
I'll be the first to accuse myself of being DBA-centric in approaching this install, and I think it has given me a misconception on how best to approach this.
We were initially thinking about separating the Report Server piece which runs on a server with IIS, from the underlying database which supports it (basically, the instance which contains the ReportServer and the ReportServerTempDB databases). The idea was that we could eventually place some additional data on that same SQL instance that was scrubbed for reporting, etc, and we wouldn't want to keep that information on a machine also sporting a webserver. But essentially, the DBA in me thought ... keep any SQL Instance off of a webserver.
But, we have plenty of other SQL Instances which could handle the addition of that scrubbed data. So then it dawned on me that maybe I should be installing SQL Server instance and SSRS on the same machine, with that SQL Server instance only handling the ReportServer and the ReportServerTempDB databases, with all of their other SQL databases sitting on other servers.
Is that the standard approach, is to install SSRS and a SQL Instance on the same server, with the SQL Instance in this case being just a container for the 2 Report Server databases, similar to an SCCM installation, or a TFS installation?
Thanks,
--=Chuck
January 11, 2018 at 11:27 am
SSRS does not require IIS. It has a web server is built into it.
I have SSRS sitting on the same server as a few terabyte-sized databases for easy management. There are 700 reports and 400 subscriptions (although I do not know the usage and frequency). ReportServer database is about 350MB and ReportServerTempDB is about 2GB. No performance issue at all except spikes at month ends.
Moving report server is quite an easy task. So it won't hurt if you start on the same server and relocate them down the track if necessary. Just take care of the encryption key.
January 11, 2018 at 12:12 pm
Thanks for the help.
Maybe this is a more pointed question, that I just haven't found an answer to yet:
Is the typical setup usually to put SSRS, and the SQL Server instance which contains the ReportServer and ReportServerTempDB, on the same server?
Then, the variance in installation comes when deciding to place the databases which furnish the reports' data in that same SQL Instance, or in SQL Instances on separate servers?
--=cf
January 11, 2018 at 12:23 pm
The technical answer is - it depends...
If you plan on scaling the solution out to support a large number of reports and/or users then I would recommend separating the databases from the web services. This would allow you to add additional web servers as needed (scale-out is Enterprise Edition only).
This is also a cost related issue - as all servers must be fully licensed. That is - you cannot install the database portion to one server - and the reporting services to another server without licensing both servers. If both servers have 4 CPUs you would need 4 2-packs to cover both servers...if you install on a single server with only 4 CPUs you only need 2 2-packs.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 11, 2018 at 12:29 pm
chuck.forbes - Thursday, January 11, 2018 12:12 PMThanks for the help.Maybe this is a more pointed question, that I just haven't found an answer to yet:
Is the typical setup usually to put SSRS, and the SQL Server instance which contains the ReportServer and ReportServerTempDB, on the same server?Then, the variance in installation comes when deciding to place the databases which furnish the reports' data in that same SQL Instance, or in SQL Instances on separate servers?
--=cf
I do not know what a 'typical' deployment would look like. In my case, SSRS and its two databases (ReportServer and ReportServerTempDB), and the actual databases supplying data to the reports, are all on the same server. I have not seen any performance issue.
The nice thing about SSRS is that you can scale out deployment by adding additional reporting servers to the mix - a built-in easy-to-use feature. If you open Reporting Services Config Manager you will see that option (on the left if you are using SQL2014). it's gone from SQL 2016 reporting services though. I haven't had a chance to look for it.
January 11, 2018 at 1:23 pm
It was the licensing piece that caused me to rethink my assumptions, 8 licenses vs 4 licenses. We are using SQL Standard too, so we aren't going to be licensed for an SSRS server farm.
Since we'll have a pretty small setup, I think we'll setup SSRS and the supporting SQL Server Instance with the 2 databases on the same machine, and then have the reports query existing data in completely separate SQL Server instances (since those other instances already exist). If this sounds like I've mis-interpreted your helpful suggestions, please let me know.
--=Chuck
January 11, 2018 at 1:55 pm
chuck.forbes - Thursday, January 11, 2018 1:23 PMIt was the licensing piece that caused me to rethink my assumptions, 8 licenses vs 4 licenses. We are using SQL Standard too, so we aren't going to be licensed for an SSRS server farm.Since we'll have a pretty small setup, I think we'll setup SSRS and the supporting SQL Server Instance with the 2 databases on the same machine, and then have the reports query existing data in completely separate SQL Server instances (since those other instances already exist). If this sounds like I've mis-interpreted your helpful suggestions, please let me know.
--=Chuck
Are all instances on the same server? if so, you may want to merge all into a single instance to reduce overhead. We have very heavy reports and I have never had any report-lated resource issues when everything is running within the same instance. You always have the option of moving them away when needed.
January 11, 2018 at 2:46 pm
Because we may allow external web access to the server running SSRS, we wouldn't want to place our data in SQL Server on the same machine.
--=Chuck
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply