Forum Discussion
Data Topology Setup and Current Issue
HI Everyone,
I am tasked with designing and organizing our data within multiple sources and then displaying in Power BI. The bulk of our data is coming from SQL but in a unique way. We contract with a specialized company that offers a SQL replicated server for the data entry point that we use from their service. This SQL database is frequently updated and somewhat controlled by them, but is located on-premises at our company. So we can have as much control as possible with our data, I built another SQL instance and just linked the server and create views in the new instance but pulling data from the other instance. While I am building the foundation of the data in our controlled instance, I have the instance located on my computer. We will be moving this instance to a Azure VM with SQL.
Right now on my local computer, when I am accessing my SQL database through Azure data studio and altering code, I cannot refresh data through Power BI desktop on my local computer. I get a network releated instance error. The vice versa is true as well when I start in Power BI desktop and try to access my SQL database in ADS. My question is, will this change where I can be in both at the same time when I switch to the SQL instance being in Azure VM with SQL?
Is there a better data topolgy than this or is there something I am missing on my local computer now that would allow me to be in both the SQL database and Power BI accessing the SQL database without getting errors? Any help is very appreciated with this problem.
Hi dbrandone ,
You are connecting to a SQL Server that hosted in Azure VM but not Azure SQL database in Power BI Desktop. For the former, gateway is required, you can install gateway on the same machine with Power BI Desktop, then add SQL Server data source within the gateway. Otherwise, you are not able to view the published report in Power BI Service.
For Azure SQL database, gateway is not required.Refer to:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi dbrandone ,
You are connecting to a SQL Server that hosted in Azure VM but not Azure SQL database in Power BI Desktop. For the former, gateway is required, you can install gateway on the same machine with Power BI Desktop, then add SQL Server data source within the gateway. Otherwise, you are not able to view the published report in Power BI Service.
For Azure SQL database, gateway is not required.Refer to:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Evelyn,
Currently, I have one instance on my local computer and I connect that instance through linked server with another SQL server stored on premises on a server. I cant go the route of Azure SQL database since it does not allow linked server access. When I talk about a new setup, I will be migrating my instance on my local machine to either SQL server on another on premises server or on an Azure VM with SQL. Either way I go, I will still need to use linked server to access the current on premises sql server. Either way I go, I know I will need to have a gateway setup for connection to Power BI. I wanted to know if I technically had my VM open or RDP for on premises server and SQL server open and changes being made, and also had power Bi open with my laptop, if I refreshed the power bi data, would I run into network instance errors like I am now with having SQL and Power BI located on my local computer.
