Small and medium business | Business Central, N...

Is it possible to replicate Dynamics 365 Business Central Saas to either Azure SQL or On-Prem SQL

(4) Share

Report

Posted on by Warric

We are planning on moving to cloud BC but we currently connect directly to our On Prem SQL backend for quite a few operations.

Is it possible to replicate the Dynamics BC Saas onto Azure SQL or On prem SQL to be able to make use of the raw data table in the same way?

Thanks

Warric

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Inge M. Bruvik 1,165 Moderator on at

Like (1)

Report
Copy link

Link copied!

If you are looking at a way to actually replicate the data in a way that you can use the BC online data as a BC on-prem database then there is no solution for that.

If you just want to store local data for reporting purpose and so on that is possible in various ways depending of your need.

Was this reply helpful? Yes No
Warric 33 on at

Like (0)

Report
Copy link

Link copied!

Hi Inge

Thanks for the response.

Yes we currently are using the On Prem DB to connect to and report on directly (via SSIS/PBI).

My thoughts where that one way would be to replicate the Online DB to Azure SQL/On Prem SQL daily (or specific tables) and then report on those tables via Azure Synapse etc.

What are the various possibilities to access our data on a similar level e.g GL table, Item Ledger Table etc. if our data was stored on BC Saas for granular level reporting and customization similar to what is available with direct table access?

Was this reply helpful? Yes No
Suggested answer

Inge M. Bruvik 1,165 Moderator on at

Like (2)

Report
Copy link

Link copied!

Hi Warric!

The easiest way to get your data out is using API's there are many standard API's you can use to access your data. You can also create custom API's for data that is not covered by the standard API's.

So using API's to extract data and store them in a local DB og Azure DB is not very hard. The API is used by the standard system for reporting in Power BI.

You can also sync your data with Dataverse (common data service) i would recommend that way if you are working with really large amount of data that needs to be frequently updated.

Some documentation on the API's

docs.microsoft.com/.../

And some on Business Central an dataverse:

docs.microsoft.com/.../admin-common-data-service

You can also export your online database and set it up on prem - but that is a manual process for each time and not very good for regular sync

docs.microsoft.com/.../tenant-admin-center-database-export

I hope this helps you further.

Was this reply helpful? Yes No
Suggested answer

Rob F. 516 on at

Like (0)

Report
Copy link

Link copied!

Hi Warric,

This third-party app called Cloud Replicator on AppSource can do what you are looking for.

Rob

Was this reply helpful? Yes No
Suggested answer

MahGah 15,593 on at

Like (0)

Report
Copy link

Link copied!

Mr Inge provided all out of the box solutions.

The other ways is to use app such as below or services like the second link. Or vote for the Direct SQL access (third link)

appsource.microsoft.com/.../PUBID.efoqus-5058796|AID.replicator|PAPPID.18a4d438-88d1-44ee-a38d-3c0ea0d77338

lyftron.com/.../

experience.dynamics.com/.../

Was this reply helpful? Yes No
Valery Moskalenko 78 on at

Like (0)

Report
Copy link

Link copied!

You can use the following solution microsoft/bc2adls: Exporting data from Dynamics 365 Business Central to Azure data lake storage (github.com)

Was this reply helpful? Yes No
AP-12011004-0 2 on at

Like (0)

Report
Copy link

Link copied!

Hi Warric,
yes – even with Business Central SaaS you can still work against a local or Azure SQL database by replicating the data out of BC instead of connecting directly to the BC tenant database (which is not supported on SaaS). You typically do this via the standard APIs/OData and push the data into your own Azure SQL or on‑prem SQL, on a schedule (near‑real time or batch), then point your SSIS/PBI/reporting tools to that database.

If you’re looking for something managed that does exactly this, I’ve built BC2SQL Sync (https://www.bctosql.com), a SaaS designed for companies moving from BC on‑prem to BC online who still need a raw SQL database. It syncs Business Central Online data into SQL Server (Azure or on‑prem) with incremental loads, scheduling and multi‑company support, so you can continue to use your existing SQL‑based integrations and reporting while running BC in the cloud.

Was this reply helpful? Yes No