I apologize in advance if this is the wrong place to post this question. We are just starting out on our Power BI journey, and so far have been fine with Reports based on Sharepoint hosted Excel Data sources. However, we'd like to start doing reports from an Enterprise software that runs with an internal SQL database, so we are at the phase of setting up a Gateway for this environment.
The main concern we have is not affecting the performance of the people using the software throughout the day. So we've been debating on a few options:
- Setup a 2nd SQL server, where Power BI reports look at data snapshots from a nightly maintenance plan.
- Setup a Reports instance on existing SQL server, with same snapshot plan (saving license $)
- Use the Gateway to just connect to the Live database
I'm wondering if the first two options are overkill, and the normal practice is to have the Gateway on a system that is not the SQL server. My only hesitation is that it is still unclear to me which system is doing the heavy lifiting during a refresh.
Is it the Gateway system (therefore needing more cores/memory), or is the SQL server still doing a lot and we may get performance issues during refreshes? For reference, the SQL server is a VM, licensed for 4 cores and has 96 GB Ram (live DB instance is capped at 64GB usage)
