How to Sync On-prem Database to Dynamics 365 Online

A growing company has been using Microsoft Dynamics CRM on-premises, integrated with their ERP (Dynamics NAV), SharePoint and Accounting systems. Sure, they see the benefits of moving to D365 online and Azure cloud. But Microsoft is not providing a practical solution to sync on-premises databases (I.e. Dynamics CRM) with Dynamics 365 online. Here’s their solution.

Migrating from Microsoft Dynamics CRM on-premises to Dynamics 365 online becomes more attractive as the Software as a Service (SaaS) model 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.

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

Generally, exposing back-end databases of SaaS services to the outside world is considered bad practice and can bring considerable security risks. Therefore, accessibility to the back-end database of D365 online is not provided. Instead, to perform data interaction with D365 online, Microsoft offers the official API services.

API services bring the benefit of providing internal business rules and constraints. In this way ensuring 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 dedicated to digging into documentation to set the correct parameters and make the appropriate calls to the methods / functions provided by such interfaces.

On the other hand, what if you could simply leverage your SQL skills and interact with D365 API as you would with a normal DBMS (database management system)?

Welcome to CB Dynamics 365 On-prem to Cloud Sync Solution.  “CB” stands for Connect Bridge. And it is a fundamental part of the solution for syncing on-premises database(s) to the D365 cloud.

Connect Bridge translates the ANSI standard SQL syntax into the appropriate API calls. This keeps your target system integrity secured. In addition, offering you 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 operate data into it.

Companies migrating from Dynamics CRM on-prem to D365 online will find it handy in situations where entire processes were written against the On-Prem SQL Database. Plus, now they can be reused 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 a very easy way. Below is a selection of off-the-shelf connectors.
We are going to focus on the Dynamics 365 connector.

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.

I am working for a company that uses Microsoft Dynamics CRM On-Premises and we are in process of migrating to Microsoft Dynamics 365 Online. We have several integrations directly from our datawarehouse 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:

I am working for a company which has a proprietary ERP System with 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 automated way and keep consistent both sides.
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.

Not convinced yet? Don’t worry, 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.

Jefferson Kenji Takahashi, MBA, Solution Architect, Connecting Software