How to Sync On-prem Database to Dynamics 365 Online

Ana Neto Company news, Connectors, Products and Solutions, Technical 3 Comments

Article last updated on: May 05, 2020

A growing company has been using Microsoft Dynamics CRM on-premises, integrated with their ERP (Dynamics NAV), SharePoint and an accounting system. Sure, they see the benefits of moving to D365 online and Azure cloud. But Microsoft is not providing a practical solution to sync the on-premises databases of Dynamics CRM with Dynamics 365 online. If Microsoft doesn't help, how can they sync data with Dynamics 365? Here’s their solution to connect to the Dynamics 365 database.

Move Dynamics CRM On-prem to D365 Online

Although it is still possible to have a Dynamics 365 on premise deployment, migrating from Microsoft Dynamics CRM on-premises to Dynamics 365 online seems more attractive for most companies these days.

As they consider Dynamics 365 online vs on premise, they see the Software as a Service (SaaS) model of Dynamics 365 online brings clear benefits:

  • You do not have to purchase hardware or infrastructure, and you do not have to maintain it;
  • The software is typically licensed on an ongoing pay-as-you-go model, rather than a big one-time, per-seat purchase;
  • SaaS often offers universal access, allowing you to connect to the software from any device (desktop, laptop, mobile phone) from any place that has an Internet connection;
  • The initial price is usually considerably lower than purchasing on-premises equipment and software;
  • High Availability SLA (i.e. Microsoft Dynamics 365 99.9% uptime SLA)

So, syncing on-premises databases to Dynamics 365 online should be pretty easy, right?

Ideally, to migrate from Microsoft Dynamics CRM On-Premises to D365 online, you would be able to transfer the data directly from the local Microsoft Dynamics CRM SQL Database backend to the remote Microsoft D365 SQL database. And if, by any chance, you already had D365 and wanted to migrate Dynamics 365 on premise to online it should be even more straightforward.

In practice, however, it’s not so simple.

What you get from Microsoft to move to Dynamics 365 online

Currently, to help customers deploy Microsoft cloud solutions, Microsoft offers support via the FastTrack page. Customers with eligible subscriptions to Microsoft 365, Dynamics 365 or Azure, can use FastTrack at no additional cost during the life of their subscription (link at the end of the article).  You need to check the current eligibility criteria for what you are trying to migrate. Microsoft sometimes bases it on the number of seats, sometimes on the revenue.

In April 2019, Microsoft also released "Microsoft Dynamics CRM on-premises to Dynamics 365 Online Migration", a 45-page long PDF document that describes what you need to do (download link at the end of the article). In this document, they confess that "Until recently, there has been no mechanism available for customers to easily move their on-premises deployment to Dynamics 365 (online)."

Problems to Sync On-prem with Online Databases

You are thinking the ideal solution would be to find a Dynamics 365 database synchronization with your Dynamics CRM on-prem.

The problem is that, generally, exposing back-end databases of SaaS services to the outside world is considered bad practice and can bring considerable security risks. Therefore, Microsoft does not provide access to the back-end database of D365 online. Instead, to perform data interaction with D365 online, Microsoft offers the official API services.

API services bring the benefit of not breaking internal business rules and constraints. In this way, API services ensure that client applications do not hinder performance. Moreover, they help preserve data integrity and consistency of the back-end database.
Developing processes to manipulate data through API’s requires programming skills and time. In particular, time to dig into the documentation to set the correct parameters and make the appropriate calls to the methods or functions provided by such interfaces.

What if you could simply leverage your SQL skills and interact with D365 API as you would with a regular DBMS (database management system)?

What if you could just have Dynamics 365 synchronize the database with SQL Server Management Studio?

Connect to D365 Online from Local DBMS or any Client Application

Welcome to D365 Database Sync Solution! This is a specific solution for syncing on-premises databases to the D365 cloud. It is based on Connecting Software's integration platform Connect Bridge.

Connect Bridge translates the ANSI standard SQL syntax into the appropriate API calls. By ensuring the access to data is done with API calls (although you don't have to worry about it) keeps your target system integrity secured. Plus, you also get the ease of communicating with any target system via the standard SELECT, INSERT, UPDATE and DELETE statements.

Therefore, from your local DBMS or any client application, you can connect to D365 online and put data into it.

For companies migrating from Dynamics CRM on-prem to D365 online it is especially handy in situations where they had entire processes written against the on-prem SQL Database. Fortunately, now they can be reused by adding Connect Bridge into their portfolio.

Now it's time for a practical example.

From any Database to Dynamics 365 Online

In this example we are going to use the famous Adventure Works DW (Datawarehouse) database from Microsoft (download it here). We'll demonstrate how you can easily integrate your existing processes with Microsoft Dynamics 365 Online (info here) using the Connect Bridge Platform.

If you are using SSMS (SQL Server Management Studio) to administrate your SQL Server Database, you can see the schema of the database in the Object Explorer window.

Schema of database in the Object Explorer window

Connect Bridge Platform allows you to create Linked Server connections to any target system via connectors in an effortless way. There are 400+ connectors readily available. We are going to focus on the Dynamics 365 connector. Below is a selection of the connectors in the CRM/ERP/Accounting area.

Connectors - CRM / ERP / Accounting area

Below you can find the Linked server connection already created in SSMS.

Linked server connection already created in SSMS

Note that we purposely named the linked server connection D365_CRM. And, by expanding the catalogs node in the TreeView, we can see the entire schema provided by Dynamics 365, just as you would see by accessing a traditional database.

With that in place, you are able to create any kind of integration or customize any existing processes leveraging usability.

Applicability of On-prem to D365 Cloud Sync Solution

Now, you might be wondering: why would I need to do this in the first place?

Here are two common scenarios that you may relate to.

Scenario 1:

I am working for a company that uses Microsoft Dynamics CRM On-Premises, and we are in the process of migrating to Microsoft Dynamics 365 Online. We have several integrations directly from our SQL Server databases to the SQL Server database of Microsoft Dynamics CRM On-Premises.

But, I can't connect with Microsoft Dynamics 365 Online database directly:

  • Will I have to create from scratch all my processes using the Dynamics CRM SDK from Microsoft?
  • Will I have to create from scratch all my processes using the Web API?
  • Do I have to learn C# to do all of this?

Scenario 2:

I am working for a company which has a proprietary ERP System with a SQL Server database as backend. We decided to adopt Microsoft Dynamics 365 CRM as our Customer Relationship Management System.

We would like to be able to populate the CRM with existing data from our ERP System in an automated way and keep both sides consistent.

Doing this manually seems almost impossible since we are talking about tens of thousands of records per entity (Accounts, Contacts, Products to name a few). Also, it is comfortable to work with the SQL language (Structured Query Language).

However, we do not have deep experience with other programming languages and frameworks (C#, Web API’s, Rest Services, etc.):

  • Will we have to hire a consultancy company to do this for us?
  • Will I depend of this consultancy company to maintain and add new features as my business change / grow?

Thankfully, the answer for all the above is no. At this point the usage of the example above might be ringing a bell.

With the approach presented you can leverage your existing skills and processes massively.

Are you not convinced yet? Why don't you check the step-by-step example below?

Populating Dynamics 365 Online Accounts from Adventure Works Database Table DimCustomer.

Create the following triggers:


CREATE TRIGGER [dbo].[OnDimCustomerInsert] ON [dbo].[DimCustomer] AFTER INSERT



       SET                               NOCOUNT ON


       DECLARE @SQL                      VARCHAR(255)

       DECLARE @AccountId                VARCHAR(255)

       DECLARE @FirstName                VARCHAR(255)

       DECLARE @LastName                 VARCHAR(255)

       DECLARE @MiddleName               VARCHAR(255)


       SELECT @FirstName       = [FirstName],

              @LastName        = [LastName],

              @MiddleName      = [MiddleName]



       @FirstName + '-' + '-' + @LastName)


       CREATE TABLE #AccountId(ID VARCHAR(255))

       INSERT #AccountId

       EXEC(@SQL) AT D365_CRM


       SELECT @AccountId = ID from #AccountId

       UPDATE DimCustomer SET CustomerAlternateKey = @AccountId where FirstName = @FirstName and MiddleName = @MiddleName and LastName = @LastName


       PRINT @FirstName + ' ' + @MiddleName + ' ' + @LastName;



CREATE TRIGGER [dbo].[OnDimCustomerUpdate] ON [dbo].[DimCustomer] AFTER UPDATE



       SET                               NOCOUNT ON


       DECLARE @SQL                      VARCHAR(255)

       DECLARE @AccountId                VARCHAR(255)

       DECLARE @FirstName                VARCHAR(255)

       DECLARE @LastName                 VARCHAR(255)

       DECLARE @MiddleName               VARCHAR(255)


       SELECT @AccountId       = [CustomerAlternateKey],

              @FirstName       = [FirstName],

              @LastName        = [LastName],

              @MiddleName      = [MiddleName]



       SELECT @SQL = FORMATMESSAGE('UPDATE ACCOUNT SET Name=''%s'' WHERE ACCOUNTID=''%s''', @FirstName + '-' + '-' + @LastName, @AccountId)

       EXEC(@SQL) AT D365_CRM;

       PRINT @FirstName + ' ' + @MiddleName + ' ' + @LastName;



Your SSMS Object Explorer window should contain now 2 new triggers as following:

two new triggers in SSMS Object Explorer window

Open your Dynamics 365 Online instance and navigate to the module Sales -> Accounts

Dynamics 365 Accounts


In SSMS open a new query window and paste the following:

INSERT INTO [AdventureWorksDW].[dbo].[DimCustomer] (FirstName, MiddleName, LastName, CustomerAlternateKey) values ('ABC Technology', '' ,'GMB', 'CT0001');

INSERT INTO [AdventureWorksDW].[dbo].[DimCustomer] (FirstName, MiddleName, LastName, CustomerAlternateKey) values ('Billy Charity', '' ,'SA', 'CT0002');

INSERT INTO [AdventureWorksDW].[dbo].[DimCustomer] (FirstName, MiddleName, LastName, CustomerAlternateKey) values ('Tom Cat', '' ,'ORG', 'CT0003');


SELECT * FROM DimCustomer WHERE FirstName = 'ABC Technology';

SELECT * FROM DimCustomer WHERE FirstName = 'Billy Charity';

SELECT * FROM DimCustomer WHERE FirstName = 'Tom Cat';

Run the scripts by pressing F5 or the Execute button in SSMS.

You should have now 3 additional records inserted into the DimCustomer Table.

three additional records inserted into the DimCustomer Table

Navigate back to your Dynamics 365 Sales -> Accounts and refresh the screen. Bingo!

three new accounts created in Dynamics 365

3 new Accounts were created in Dynamics 365, cool right?

But wait there, in the table DimCustomer the field CustomerAlternateKey has a strange number. It isn’t the values we have inserted in the first place.

Instead we have the unique ID of the account generated in Dynamics 365. How is that possible?

If you look closely at the trigger OnDimCustomerInsert you will notice that SELECT SCOPE_IDENTITY(); is the responsible to return the account id that will be used to update the table DimCustomer.

That will be crucial to perform updates on existing accounts in Dynamics 365 when a record is changed in the table DimCustomer.

To prove our point, update the records created by executing in SSMS the following code:

After executing the updates for the 3 accounts here is the result in Dynamics 365.

UPDATE [AdventureWorksDW].[dbo].[DimCustomer] SET LastName = 'LTD' WHERE CustomerAlternateKey = 'account(9a1bc5cb-6ad7-e811-a96f-000d3a2cb90b)';


Dynamics 365 Accounts after executing updates



The examples above are very simple, however, it is not hard to visualize the benefits of such an approach.

  • Maintenance of the process is centralized and isolated
  • Enhancing the process by modifying the triggers is a breeze
  • There is no need to learn any new technology / programming language

That’s it. I hope you have enjoyed.

If you have comments, suggestions, write them in the space below and we'll respond ASAP.

I hope you have as much fun using Connect Bridge Platform as I had writing this article.

Why Customers use Connect Bridge

Watch a short video to find out:

Connect Bridge presentation video

Article last updated on: May 05, 2020

Ana Neto - Technical advisor, Author

Ana Neto
 technical advisor at Connecting Software


I have been a software engineer since 1997, with a more recent love for writing and public speaking.  Do you have any questions or comments about this post or about Connect Bridge? I would love to have your feedback!

Contributor: Jefferson Kenji Takahashi, MBA

Comments 3

  1. Looks interesting – what about business rule enforcement? e.g. we have a need to get transactions from our POS (data in SQL tables) into Business Central – so what about the use case where the sale involves a customer and or product which does not exist in D365?

  2. Post

    Hi Paul,

    If you can get the data you have from your POS in the SQL tables and it follows a structure you can understand, then you can use Connect Bridge to get everything into Business Central. If the sale involves a customer and or product which does not exist in D365, you need to decide what to do, for example you could use a generic customer or product or you could create new ones on the fly.

    We will get in touch with through the e-mail you indicated to explore this option with you.

    Ana Neto

Leave a Reply

Your email address will not be published. Required fields are marked *