Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Best practise architecture

I'm planning to install and configure Power BI Premium within my organization.

 

I would like to have the following architecture, but would like your ideas about it. Are there any best practises besides the information on https://docs.microsoft.com/en-us/power-bi/report-server/capacity-planning

 

(all virtual machines)

1. A stand-alone (web)server for installing Power BI Reportserver (server 1)

2. A stand-alone (or new instance on existing SQL Server) SQL server for the Reportserver databases (server 2)

3. A stand-alone SQL Server for the source databases (server 3)

4. A stand-alone SQL Server for the Datawarehouse and DataMarts (server 4)

 

Where do I have to install the Power BI Data Gateway? I prefer to install it on a separate vm (server 5) because of the ports which should be opened to let the gateway work.

 

What do you think about this? Is this ok or is it too much separated? Hosting the features on different machines will make sure CPU and memory are going to right resources, right.

 

 

Last question: when we purchase Premium P1 we're licensed for 8 cores. Are those cores based on the reportserver server (server 1) or on the server where the reportserver databases (server 2) are located?

1 ACCEPTED SOLUTION

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
JanV
Resolver I
Resolver I

What would be the difference between 3 & 4, aren't these all source databases? What would be the function of server 3?

3. A stand-alone SQL Server for the source databases (server 3)

4. A stand-alone SQL Server for the Datawarehouse and DataMarts (server 4)

Anonymous
Not applicable

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.

 

 

Anonymous
Not applicable

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...

 

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.

 

 

 

v-qiuyu-msft
Community Support
Community Support

Hi @Anonymous,

 

1. To install the on-premise data gateway, the machine needs to meet requirements listed in this article: https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem. 

 

2. The Power BI Premium P1 8 cores are devided to 4 backend cores and 4 frontend cores, for more infomration, see: https://docs.microsoft.com/en-us/power-bi/service-premium#capacity-tiers

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks for your answer Qiuyun Yu. So i have to set up the Reportserver itself with 4 cores and the SQL server reporting database server with 4 cores also?

 

Besides, im still wondering - and documentation says nothing about it - is where do I have to install the Reporting databases.. Its an architectural question. Is it OK to put them on a SQL Server with multiple instances already on it, or is het better to create a whole new sql server, dedicated for the Reporting databases? Or is that 'overkill'?

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.