Power BI On-premise gateway - Transient error failures relating to SQL server instance

[commercial_user]

Hello all,

My question is whether on-premise gateway is well suited for multiple people using the same Gateway that is configured to a single SQL server instance or in this case the same database data source?

My use case is that there are several different semantic models on various workspaces that may have the same refresh schedule. Lately we are having transient errors that occur using the Configured Enterprise Gateway that fails to access the dataset due to a table being inaccessible on the same SQL server.  There is no consistent error that can determine the root cause of why a refresh fails but the issue seems to resolve itself when using a different schedule time then other semantic models or invoking a on-demand refresh.

Is there a correlation between the shared on-premise gateway on a single SQL server instance that should prompt us to make a seperate gateway for each use case? 

The issue is that we have a database that has hundreds of tables, many that store data that are not all related to each other (Muiltiple sources) in a single database instance. Each data source owner then uses the same gateway to refresh their own data source. 

Please let me know what is the best approach that should resolve this issue?

[Anonymous]

Hi @commercial_user ,

 

Is my follow-up just to ask if the problem has been solved?

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

[Anonymous]

Thanks for the reply from lbendlin, please allow me to add some more information.
Hi @commercial_user ，

You can refer to the following link to learn about the gateway cluster.

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

Additionally, if your workspace uses premium capacity, then you can check to see if the refresh fails due to the parallel refresh limit being reached.

Semantic model SKU limitation

Best Regards,
Dengliang Li

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

[lbendlin]

You will want to consider creating a gateway cluster.  Both for business continuity and also for load balancing.

You will want to make sure the gateway VMs have low latency to the database server.

Gateway VMs work best with 16GB of RAM (or more) and 8 cores (or more)

The database server needs to be tuned to accept large number of concurrent connections.

If your main workload are refreshes then try to use SSDs for the gateway drives.  (not as relevant for Direct Query)