Forum Discussion
Best practise architecture
Hi Anonymous,
With one P1 instance, you can run the Power BI Report Server software itself on up to 8 cores on-premises; that could be one report server on an 8-core machine, or two scale-out report servers on two 4-core machines. Via the “SQL Server Technology” use right, you can run SQL Server Standard on any number of cores, same machine or a different one, solely to store the report server database. (Alternatively, you can bring your own SQL Server license and use the database engine for whatever you like.)
Regarding report server database requirements, see: https://docs.microsoft.com/en-us/power-bi/report-server/system-requirements#database-server-version-requirements
Best Regards,
Qiuyun Yu
I would like to have a dedicated server for the datawarehouse and datamarts, so i have the flexibility to change specific settings (like resources) or the SQL Server edition itself. Performance will be key for the customer so i dont want it to be dependent or shared with other SQL databases/instances on that server.
With server 3 I mean all kind of production (OLTP) databases. ETL processes will get data from those databases and fill the DWH on server 4.
If you are only accessing on premise data sources you don't need a data gateway. You can just hit them directly exactly as you can with SSRS.
Be aware that PowerBI-SSRS is not just a web app. In the background it spins up captive SSAS TAB instances for PBIX files that have imported data. These captive instances can consume quite large quantities of memory if you get a number of them running concurrently (500-1000Mb per captive instance is not uncommon on our stuff, one instance per active report) so you may need more to give these boxes some memory so they can breath if you have moderate to high concurrency.
A slightly longer explanation of what happens is included here https://community.powerbi.com/t5/Report-Server/Needed-instances-for-PowerBI-report-server/m-p/445068#M6194
The web services that are part of PBI-SSRS that provide the data to your rendered client apps are also quite chatty. Every time a user slices/hilights/drills on a visual/page a set of queries is sent to the backend data source, either a captive SSAS instance or the actual backend if the PBIX is using Direct Query or Live connection. So be prepared for a fair amount of network chatter between the PBI-SSRS and your data sources if you are using Direct Query or Live connection.
We use Live Connection onto TAB models for all our reporting. So we have pushed our SSAS TAB instances onto the same servers as the PBI-SSRS front ends. We need a SQL License for each server anyway so running SSAS on there is no trouble. We process the Tabular on a backend server then backup the TAB DBand restore it to the front facing SSAS servers that also have PBI-SSRS on them. All the query traffic then stays on the same server. Data sources use localhost. We then stick a layer 5 network DNS over the top of the PBI-SSRS servers (split across data centres for redundancy) with sticky sessions so users get distributed between the two. (we only have 2 right now though we may well roll out more) We aren't using SSRS scale out at all.
We have placed the ReportServer DB on an AG (again split across the data centres) and all the PBI-SSRS front ends use the AG listener for this.
We originally had the PBI-SSRS on separate boxes as an L5 group and the SSAS backends on another L5 group. This generated an awful lot of traffic through the L5 switches and it got very difficult to predict what would be working if we took any given server out for patching etc. User requests could bounce from data centre to data centre as they travelled down the stack. It's worse in our case cos we also have a web portal app in front of the PBI reports so that was yet another L5 bounce.