How do you get real-time data out of a local SQL server?

[phk_holship]

Hi.

We have all our data on a local SQL server. I have set up a PowerBI report that feeds off those data, but because they are local, the reports are only refreshed as long as my own PC is online, because it acts as a gateway.

What would be the right way to go about it, if we wanted a real time updating report, without the need of having PCs standing around in the office acting as gateways? I was thinking something like Azure SQL, maybe? But I am clueless about these things, hoping for some good advice.

Or maybe there is some smart service that can make copies/backups of our database to the cloud, and from there you could attach a powerBI flow.

[ibarrau · Accepted solution]

Hi. First of all, if the source is a local SQL Server you must use a gateway. That means that even if you use Direct Query (querys to source with 15 mins cache, near real time) or Import Data, you must set up a Machine with the Gateway for refreshes. The best approach would be having a Machine or a Virtual Machine running 24x7 with the gateway installed in order to run refreshes or querys whenever you want.

Azure can let you create databases as services. If you are thinking about migrating the sources to the cloud, then you can avoid the gateway and just refresh Power Bi without any Gateway. The service is Azure SQL Database.

I hope that helps,