How to Sync On-prem Database to Dynamics 365 Online

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.

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:

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.

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).”

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?

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.

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.

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.

Below you can find the 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.

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:

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.):

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?

Preparation
Create the following triggers:

–*****************************************************************************

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

AS

BEGIN

       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]

       FROM INSERTED;

       SELECT @SQL = FORMATMESSAGE(‘INSERT INTO ACCOUNT (Name) VALUES(”%s”); SELECT SCOPE_IDENTITY();’,

       @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;

END

–*****************************************************************************

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

AS

BEGIN

       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]

       FROM INSERTED;

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

       EXEC(@SQL) AT D365_CRM;

       PRINT @FirstName + ‘ ‘ + @MiddleName + ‘ ‘ + @LastName;

END

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

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

Testing:

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.

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

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:

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

NOTE: REPLACE THE STRING account(9a1bc5cb-6ad7-e811-a96f-000d3a2cb90b) WITH THE VALUES GENERATED IN YOUR ENVIRONMENT.

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

Sweet!

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

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.

Ana Neto
 technical advisor at Connecting Software

Author:

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!

 ana@connecting-software.com

Contributor: Jefferson Kenji Takahashi, MBA