Enterprise Gateway with External Datawarehouse

[Solle]

Hi All, 

I am fairly new to gateways in PowerBI, however I have recently been digging into this, however I was not able to find anything on what to do and how to do it properly when having an external datawarehouse.

Our current setup is like the following:
My Computer --> External Firewall --> SQL Login --> SQL Access (AWS)

In this case how would I be able to have an Enterprise Gateway to connect to the SQL Server?

Should I contact our External Datawarehouse to install it on the server or is it something that I should have on a machine (24 hour up time) internal? 

Hope that someone can help me out on this. 

Best Regards,
Solle

[v-yueyunzh-msft · Accepted solution]

Hi , @Solle 

Depending on your current data source structure: My Computer --> External Firewall --> SQL Login --> SQL Access (AWS) I think whether you configure your Enterprise Gateway installation on a server or on an internal computer, you need to ensure that two things must be done, at least to ensure the success of data refresh, and then to consider the performance aspect:

1. You need to ensure that the server configured in Enterprise Gateway and your SQL Server data source under firewall protection can successfully establish a connection using SQL Login, and you can use the nslookup command to manually test the connection connectivity:
nslookup | Microsoft Learn

Or use UDL tests to perform relevant connectivity tests.

2.Another point is that you need to ensure that Enterprise Gateway is fully connected to Azure service bus in the current server network environment, because Azure service bus is the channel for Enterprise Gateway to communicate data with the external network (PBI service), and you can do a port test on the Enterprise Gateway application:
Adjust communication settings for the on-premises data gateway | Microsoft Learn

After ensuring the smooth connection of these two points, you can start to test whether the connection between the data source and the gateway is normally configured, and if successful, we can then optimize the performance of the gateway, hardware configuration, and performance, you can refer to this document:
Monitor and optimize on-premises data gateway performance | Microsoft Learn

For the detailed introduction and description of the gateway architecture I mentioned earlier, you can refer to this official document, which contains a detailed architecture diagram introduction:

On-premises data gateway architecture | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Solle]

Hi Aniya,

Thank you for your very detailed description of the processes, that I will have to take into account before being able to setup the on-premise gateway. 

So what I hear you saying is that, as long as there is connectivity through the firewall and as long as I am able to connect through the gateway then it should work fine? 

Furthermore, do you suggest to have it on a PC at my office and let that run 24/7 or would it be better to install it directly on the AWS server?

Best Regards,
Solle

[v-yueyunzh-msft]

Hi , @Solle 

Thanks for the quick reply! According to your additional question consultation, I highly recommend that you install the gateway on a run 24/7 computer, and the computer has stable memory and CPU allocated to the gateway this background process service, because Power BI's dataset refresh may occur throughout the day, and there is a need for stable refresh and all-day refresh, so my answer to your question is Yes.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Solle]

Hi @v-yueyunzh-msft 

Thank you for your reply, I will try to follow your approach and mark it as a solution.

Best Regards,
Solle

[djurecicK2]

Hi @Solle ,

 This article might be helpful to you:

https://docs.aws.amazon.com/whitepapers/latest/using-power-bi-with-aws-cloud/connecting-the-microsof...

[Solle]

Hi @djurecicK2 

Thank you for your response, I think that this article says that it would be best to install it on the AWS server directly. The only thing I am unsure of is how will our firms firewall be able to allow that access, will it go out with the same IP as the server? 

Best Regards,
Solle 

Best Regards,
Jeppe Solgaard