Change the data source from on premise SQL Server to SQL Azure | What should be taken care of?

[vinaypugalia]

Hello,

I have developed Power BI reports using Local SQL Server and now the client wants to move the database to SQL Azure.

Can someone please guide what care should be taken for this move and what will be the impact on my existing visuals from this?

Thanks,

Vinay

[v-qiuyu-msft]

Hi @vinaypugalia,

As we are not able to change the data source in service directly, if you already publish the report to Power BI service before move the database to Azure, we need to change the data source in Power BI desktop then republish the report. Also we can no longer connect to Azure SQL Database directly from the Power BI service.

After republish, we need to go to the dataset settings, set the credential for the dataset connect to the Azure database. My test scenario is connect to Azure SQL database in DirectQuery mode.

Reference:
Azure SQL Database with DirectQuery

Best Regards,
Qiuyun Yu

[vinaypugalia]

Thanks for sharing this information.

Also, can you please let me know the following - 

1. The generally followed way to change the datasource from on-premise SQL to SQL Azure in Power BI.
2. What will be the impact of this change on my measures & visuals?

Thanks!

[v-qiuyu-msft · Accepted solution]

Hi @vinaypugalia,

Sorry for the late response.

1.The generally followed way to change the datasource from on-premise SQL to SQL Azure in Power BI.

The way suggested in my original post is general method to change data source. We need to change it in Power BI Desktop. We can open Advanced Editor in Query Editor, you will see the connection information like below:

You can replace this with Azure SQL connection information. You can create a new report, get data from the Azure SQL database, copy this information from Advanced Editor then replace the original report connection information.

2.What will be the impact of this change on my measures & visuals?

If the Azure database content is the same as original database, the difference is just the move to Azure. It will not affect measures and visuals as data are not changed.

Best Regards,
Qiuyun Yu