Confused about SQL Server, Enterprise Gateway and publishing reports from Power BI desktop

[Anonymous]

Dear all,

I am getting a little confused. Here is my situation:

1. I have a dashboard/ report I have developed in Power BI desktop.

2. The sources of data are inhouse SQL server databases. 

3. I publish this dashboard.

4. The refresh is scheduled once a day.

5. I have not installed any gateways.

6. Daily scheduled-refresh works fine if my desktop computer remains turned on. Scheduled-refresh fails if my computer is turned off.

As this report is designed for other users to view, how do I make sure the report updates when my computer is turned off?

I had a look at the enterprise gateway, however it seems you set up the datasets for other users to access using power bi online rather than linking to those sources when you create your data model in power bi desktop.

So I am little confused.

- How do I make sure that my dashboard which uses inhouse SQL server data will refresh overnight if my local computer is turned off?

- If I need to install a gateway on a server that is fine, but then how do I link to those now cloud based sql server datasets when I am constructing my report/dashboard in power bi desktop?

Thank you for your help?

Kind regards,

Ben

[ankitpatira]

@Anonymous Regardless of whether you use gateway or not you will have to ensure data source is available online at the time of refresh. I would suggest you host sql server database on a server that is never offline and not your laptop. 

1. Schedule refresh will always fail if at that time laptop hosting data source if offline.

2. Once you install gateway on server where your sql database is and your report is published to power bi service, you can have to configure gateway first and then published datasets to use that gateway.

[Anonymous]

Hi,

The SQL server is not hosted on my PC. It is in a SQL server. However if my computer is turned off then refresh will fail. I thought the published report would just query the database rather than needing my computer to be turned on.

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

In your scenario, as data source is SQL Server database, you need to configure gateway to enable the connection between Power BI service and SQL Server database. When you refresh the report, you need to make sure both servers host Enterprise gateway and SQL Server database is on.

From your description, it seems that the Enterprise gateway is installed on the laptop, and SQL Server database is on another server. If that is a case, when the laptop is turned off, not connected to the Internet, or asleep the gateway won't work and the data in the cloud service will not be synchronized with your on-premises data. So the schedule refresh will be failed at this time.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[Anonymous]

Thank you. That clears things up for me.