Data Replication BC OnPrem v.s BC Cloud

[pejochto]

We have today quite a complex replication pattern for our BC reporting, it is a mess to be honest, this is because we have had conultants who just have built a new solution on top of existing and noone have actually done the dirty job and cleanout the old stuff, anyway it is working but it is a pain to inherit the environment that I now have been responsible for. I dont want to touch anything now, just want to move everything to the cloud, have everything working with Power BI as it is and complex Semantic Models who I dont even want to touch (no one knows how they are built). Therefore I need to just change the underlying part because we wont be able to do the SQL replication as it is now, and there is SQL views and Store Procedures that does the ETL and I cant find anything in the cloud that just replicates the Bronze layer to DWH where this is taking place, there are Stored Procedures that does something that dont work in Lakehouse SQL endpoint which I first had in mind, to replicate via BC2ADLS and name the bronze table the same name, but we have multicompany with eachcompany with its own BC instance, and LakeHouse as I have been reading doesnt support "Insert" and "Join" and then I wont be able to just take the code and move from DWH to the DLH. I just want to make this work and not touch to much, then when the migrating part is over I have time to deepdive and clean it up, but for now I am up for any dirty solution so that the pressure from business side wont be to big, when they need the reports after migration. Any help, creative solution or suggestions is welcome.

[Vinodh247 · Accepted solution]

You cannot replicate your current SQL based ETL (views + stored procedures) directly into Fabric Lakehouse. It is not built for that pattern. Trying to force it will waste time.

Practical but safe approach:

Keep your architecture pattern, just move the runtime:

• Use azure SQL db or MI  as your intermediate DWH layer (same as today)
• Replicate BC data using Dynamics 365 Business Central -> ADLS (BC2ADLS or datafactory)
• Load that into Azure SQL (staging/bronze tables, same naming)
• Reuse your existing views + stored procedures unchanged
• Point Power BI to the new SQL endpoint -> reports continue to work

  This works because...

  • Has zero rewrite of ETL logic
  • No need to touch semantic models
  • Supports multi-company easily (just replicate per DB/schema like today)
  • Lakehouse limitations avoided completely for now

    Then later, you can try to gradually move logic from SQL -> Fabric Lakehouse/Warehouse once you understand it.

[Vinodh247 · Accepted solution]

You cannot replicate your current SQL based ETL (views + stored procedures) directly into Fabric Lakehouse. It is not built for that pattern. Trying to force it will waste time.

Practical but safe approach:

Keep your architecture pattern, just move the runtime:

• Use azure SQL db or MI  as your intermediate DWH layer (same as today)
• Replicate BC data using Dynamics 365 Business Central -> ADLS (BC2ADLS or datafactory)
• Load that into Azure SQL (staging/bronze tables, same naming)
• Reuse your existing views + stored procedures unchanged
• Point Power BI to the new SQL endpoint -> reports continue to work

  This works because...

  • Has zero rewrite of ETL logic
  • No need to touch semantic models
  • Supports multi-company easily (just replicate per DB/schema like today)
  • Lakehouse limitations avoided completely for now

    Then later, you can try to gradually move logic from SQL -> Fabric Lakehouse/Warehouse once you understand it.

[pejochto]

Hi and thanks for the answer, I was trying to avoid involving a SQL server in this solution but realized that is the only way to go forward, I did what you suggested so your answer was spot on, tnx

[pejochto]

Hi, I am reading your answer with interest, but there is one thing I dont get. How do you copy BC2ADLS tables into a SQL, regardless which SQL you have, all the pipelines are exporting to a Fabric DWH endpoint today, and are working fine, the problem is, when we move to Cloud version of BC this connections doesnt work any more, because you cant get SQL Prod database access in Cloud version. So I dont understand how you are going to land the export from BC2ADLS into aby SQL server?

[v-dineshya · Accepted solution]

HI @pejochto ,

BC Cloud never pushes directly into SQL. BC2ADLS always lands in ADLS first. There is no native BC2ADLS --> SQL feature.

Please try below architecture.

Business Central Cloud --> Azure Data Lake Storage (ADLS Gen2) --> Azure SQL DB.

Note: Replace direct BC SQL --> DWH with ADLS --> SQL.

Please try below workaround.

Azure Data Factory :

Source: ADLS Gen2, Format: Parquet (BC2ADLS default), Delta

Sink: Azure SQL Database, SQL Managed Instance, on‑prem SQL.

Result: It supports Full INSERT / MERGE support and control over Table names, Schemas, Column mapping and Incremental loads.

Note: Instead of using fabric , please try Azure SQL.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @pejochto ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @pejochto ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh