Others

How to Replicate Dynamics 365 Business Central Data to Local SQL Server

Khadija Khan Oct 1, 2026 868 views

Question details

The user needs a method to automatically insert new customer records created in Dynamics 365 Business Central online into a local SQL Server database.

How to Replicate Dynamics 365 Business Central Data to Local SQL Server

Product: Dynamics 365 Business Central, SQL Server
Device & OS: not provided
Scenario: Automating data synchronization between a cloud-based ERP/CRM and an on-premises database.
Observed behavior: Customer records created online need to be automatically and seamlessly reflected in the local SQL Server environment.

Before you start

Before configuring data replication, ensure you have administrative access to both your Dynamics 365 Business Central environment and the local SQL Server, and verify that your network firewall allows secure external API connections or gateway setups.

Solution 1Recommended

Use Microsoft Power Automate to Sync Records

Power Automate provides built-in connectors for Dynamics 365 and SQL Server, making it the most straightforward method for automated record insertion without heavy coding.

This method requires installing the Microsoft On-premises data gateway on your local server to allow Power Automate to securely communicate with your local SQL database.

Create a new automated flow

Log into Microsoft Power Automate and create a new 'Automated cloud flow'.

Configure the Dynamics 365 trigger

Search for the 'Dynamics 365 Business Central' connector and select the 'When a record is created' trigger. Set the environment, company, and choose 'Customers' as the table.

Add the SQL Server action

Click 'New step' and search for the 'SQL Server' connector. Select the 'Insert row' action. You will need to authenticate using your previously configured On-premises data gateway.

Map data fields and test

Map the dynamic content fields from the Business Central trigger (like Customer Name, ID, Email) to the corresponding columns in your SQL Server database table. Save and test the flow by creating a new customer in Business Central.

Use Microsoft Power Automate to Sync Records

Gateway Configuration: Ensure the On-premises data gateway is online and running on a machine that is always powered on to prevent replication failures.

Solution 2

Consult the Dynamics 365 Community for API Solutions

For complex integrations requiring high volume replication, custom webhooks and APIs may be necessary, and the Dynamics 365 Community is the best place to find tailored architectural advice.

Free Microsoft Office alternative

Analyze and Manage Your Exported Data with WPS Office

While setting up your automated SQL integration, you may need to export, clean, and analyze Dynamics 365 data manually. WPS Office provides a lightweight, highly compatible alternative to Microsoft Office for all your spreadsheet and documentation needs.

1. Download WPS Office: Visit the official WPS website and install the free WPS Office suite on your computer.
2. Export data from Business Central: Export your customer records directly from Dynamics 365 Business Central to an Excel file.
3. Analyze data in WPS Spreadsheet: Open the downloaded .xlsx file in WPS Spreadsheet to easily clean, format, and verify the data before mapping it to your SQL Server.

Fully compatible with Microsoft Excel (.xlsx) formats for analyzing exported Dynamics data.Lightweight design that runs smoothly on almost any desktop device.Free to use with a familiar interface, requiring no steep learning curve.Seamless transition for users familiar with the Microsoft Office ecosystem.

Frequently Asked Questions

Can I use an On-Premises Data Gateway to connect Business Central online to local SQL?

Yes, Microsoft provides the On-Premises Data Gateway to securely bridge cloud services like Power Automate, Power BI, or Azure Logic Apps with your local SQL Server.

Is there a built-in direct replication tool in Business Central for local databases?

No, Dynamics 365 Business Central online does not have a native, one-click replication tool for local SQL Servers. You must rely on external tools like APIs, Power Automate, or Azure Data Factory to handle the data synchronization.

What is the best way to handle large volumes of data replication?

For large datasets or high-frequency updates, Azure Data Factory or custom applications utilizing Business Central's OData endpoints and APIs are recommended over Power Automate, as they are better equipped to handle heavy loads without hitting API limits or timeouts.

Office Quick Solutions