Forum Discussion
Microsoft EmployeePowerBI report server license
Solution SageYes you can install ReportServer on same or different node as well if you have another node. However in case of a seperate node , you would still need to host your ReportServer database on the SQL Server node itself which creates a lag sometimes in ReportServer renders, so its best to keep them on same.
Also with on-premise reporting with SQL Server as DB, the mode chosen may be Direct Query, so in that case as well installing on same node is better as report server dsnt take a lot of memory.
Having the Report Server node seperate is recommended as in above response in case your reports are going to be import mode with heavy storage.
Please do consider that if you still use SQL Server data for all your reports, such cross node communication b/w Power Bi report and Database node in on-premise may have a performance hit and noted issues around communication drop in case of long running reports(mainly paginated)
- d_gosbell
Super User
AbhiSSRS wrote:
Yes you can install ReportServer on same or different node as well if you have another node. However in case of a seperate node , you would still need to host your ReportServer database on the SQL Server node itself which creates a lag sometimes in ReportServer renders, so its best to keep them on same.
I don't think you can say it's always best to keep the database engine and Report Server on the same machine. It depends on the scale of your deployment. While it's true that the shared memory provider is faster than using TCP/IP - if you have a decent network you should not notice significant lag. And if you have a large Report Server deployment you would usually want to split the load by keeping the database and Report Server separate. For example our Report Server deployment has 2 Report Server nodes in a load balanced cluster that serves a few thousand users and we have the SQL database on its own high availability cluster.
AbhiSSRS wrote:
Also with on-premise reporting with SQL Server as DB, the mode chosen may be Direct Query, so in that case as well installing on same node is better as report server dsnt take a lot of memory.
Again this works well for smaller deployments, but for larger deployments you may want to direct query to an entirely different SQL server again so that the query load is separate from the report rendering which is separate from the report catalog. The report portal itself does not use a lot of memory, but if you may also have import mode pbix reports or a large user base which will require more resources. Or you could just have large databases already deployed on other servers which cannot be easily be moved to your report server.
AbhiSSRS wrote:
Please do consider that if you still use SQL Server data for all your reports, such cross node communication b/w Power Bi report and Database node in on-premise may have a performance hit and noted issues around communication drop in case of long running reports(mainly paginated)
I have not seen evidence of cross node communication being a root cause for performance issues. Usually for large reports the queries take longer to run and the rendering takes longer, but the network transfers typically scale well. And I don't recall ever suffering communication drops for no reason while executing long running reports although our largest paginated reports tops out at a bit over 300,000 rows and is usually output to Excel (and the Excel render uses a lot of resources) I don't know if AbhiSSRS is talking about reports larger than this.
- AbhiSSRS
d_gosbell completely align with your notes for large scale deployments and even I have had similar setups with query nodes seperate from Report Server nodes. I am suggesting only in case they have a direct query or small import models its fine to have all on same node!
For the last part the warning is based on an experience with our really long running Paginated reports( 16-28 hours) They were on multidimensional and are set of sub-reports stitched together (sounds bad right 🙂 ) The communication dropped on several instances and we had to rerun for success when there were not many running in parallel. What helped there was keeping query nodes and RS on same AWS instance and have our own distribution of report requests to 10-14 different nodes. The deployment was a pain too with scaleout built accordingly.
- d_gosbell
AbhiSSRS wrote:
For the last part the warning is based on an experience with our really long running Paginated reports( 16-28 hours) They were on multidimensional and are set of sub-reports stitched together (sounds bad right 🙂 ) The communication dropped on several instances and we had to rerun for success when there were not many running in parallel. What helped there was keeping query nodes and RS on same AWS instance and have our own distribution of report requests to 10-14 different nodes. The deployment was a pain too with scaleout built accordingly.
Ah ok I see where you are coming from. That is an entirely different scale of problem. I don't tend to pull anything that runs that long directly through Report Server if I can avoid it. I usually aim to make any of our visible reports run in 5-10 minutes at most. Then I have some that maybe take upto 30 mins which I put in hidden "Subscription" folders and try to run on a schedule outside working hours and deliver via email or file share. And processes that run longer than that I tend to build data extract routines that preferrably run on the source server to avoid potential network interruptions and if possible I'll build a loop that pulls smaller chunks of data at a time so that the process can be stopped and re-started without loosing everything. Although I realise that there are sometimes edge cases where this is not possible and everything needs to run in a single statement, but in general I try to follow the roughly follow the above splits if I can.
