Power BI To SQL Integration

[pankajnamekar25]

Dear All,

In my Senario multiple ple Power BI Report is connected to SSMS.

Issue is when I am refreshing Power BI It is hitting primary server of SSMS that cause big problem for client.

They are suggested to  hit secondary server every time.

is there any setting/Option avilable for this.

[v-linyulu-msft]

Hi,@pankajnamekar25 
I am glad that my answer could help you, and I appreciate you accepting my answer as the solution. This recognition of my efforts also helps others in the community with similar issues find solutions more quickly.

If you encounter any problems in the future, feel free to share them again.

Have a great day!

Best Regards,

Leroy Lu

[v-linyulu-msft · Accepted solution]

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi, @pankajnamekar25 
Thanks for reaching out to the Microsoft fabric community forum.

Based on my understanding of your needs, here are my suggestions:

If your data sources come from different locations, I recommend considering creating different data gateways for connection and then using the cluster concept to achieve load balancing. Load balancing will randomly select a gateway to handle requests. You can enable the "Distribute requests across all active gateways in this cluster" option to evenly distribute traffic across all active gateways.

Here are the relevant document screenshots:

For more details, please refer to:

Plan, scale, and maintain a business-critical gateway solution | Microsoft Learn

Manage on-premises data gateway high-availability clusters and load balancing | Microsoft Learn

If your data sources come from the same local PC, I suggest creating different database connections for different reports. This way, you can manually direct your data refresh to come from different databases.

For more details, please refer to:

 Add or remove a gateway data source - Power BI | Microsoft Learn

If I misunderstand your needs, I hope you can answer the questions raised by  lbendlin so that we can make suggestions based on your needs.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

SSMS is a client tool. No processing is happening there.  Please explain what you mean by "primary server"  and "secondary server".