Article last updated on: March 28, 2022
Let’s have a look at the following scenario:
A thriving company has been using Microsoft Dynamics CRM on-premises, integrated with their ERP (Dynamics NAV), SharePoint, and an accounting system.
After some research, they recognize the benefits of moving to Dynamics 365 online and Azure cloud. However, they also find out Microsoft does not provide their users with a practical solution for transferring the databases and all other existing integrations from Dynamics CRM to Dynamics 365 Online.
If the creator of the software does not have an answer to this question, how can this company make its wishes come true?
We are proud to present their solution to connect to the Dynamics 365 database…. without actually connecting to the database!
Move Dynamics CRM On-Prem to D365 Online
Although it is possible to perform a Dynamics 365 on-premises deployment, it’s safe to say that using Dynamics 365 online is currently the most attractive option for nearly all companies out there.
As they investigate the Dynamics 365 online vs. on-premises models, they quickly reach the conclusion that the Software as a Service (SaaS) version of Dynamics 365 online has the following benefits:
- Less stress - You do not have to purchase hardware or infrastructure, and you do not have to maintain it.
- No commitment - The software is typically licensed on an ongoing pay-as-you-go model rather than a big one-time, per-seat purchase.
- Versatile - SaaS often offers universal access, allowing you to connect to the software from any device (desktop, laptop, mobile phone), provided the user has an internet connection.
- Affordable - The initial investment is usually considerably lower than purchasing on-premises equipment and software.
- Trustworthy – The cloud solution comes with a High Availability SLA.
So, How Easy Is It to Sync On-Premises Databases to Dynamics 365 Online Then?
In a perfect world, the migration from Microsoft Dynamics CRM on-premises to Dynamics 365 online would easily include the existing integrations to Microsoft Dynamics CRM on-premises. This should be even simpler in case you already had D365 and wanted to go fort a Dynamics 365 on premise to cloud migration.
However, we do not live in a perfect world. Nevertheless, even if moving to the cloud is not as simple as you would hope, there tips and tricks that can help you along the way. Let’s get started!
What Microsoft Offers You For The Move To Dynamics 365 Online
There’s this thing called FastTrack page. Microsoft offers support through it to help its users deploy Microsoft cloud solutions. Customers with eligible subscriptions to Microsoft 365, Dynamics 365, or Azure can access it at no additional cost during the entire span of their subscription (link at the end of the article). Hence, if you intend to use this, it’s recommended that you check the updated eligibility criteria for what you are trying to migrate. Microsoft usually bases these off number of seats or revenue.
In April 2019, Microsoft acknowledged that “Until recently, there has been no mechanism available for customers to easily move their on-premises deployment to Dynamics 365 (online)”. This was confessed in a 45-page PDF document titled “Microsoft Dynamics CRM on-premises to Dynamics 365 Online Migration”, which describes the steps to follow (download link is at the end of the article too).
Can I Access The Dynamics Database?
A possible solution for that problem would be to find a way to access the Dynamics 365 database and then go for the synchronization with your Dynamics CRM on-premises. The problem is that Microsoft does not provide access to the back-end Dynamics database.
This makes perfect sense, as exposing back-end databases of SaaS services to the outside world is considered bad practice and can lead to considerable security breaches or data integrity problems. Instead, Microsoft offers an official API to perform the data interaction with D365 online.
The main reason why API services are used is that they do not break internal business rules and constraints, ensuring the client applications do not hinder performance. They also help to maintain data integrity and consistency of the back-end database. Quite beneficial, right?
The only downside is that a lot of skills and time are required for the development of processes to manipulate data through the API. Additionally, you will have to dive into tons of documentation to discover the correct parameters and make the appropriate calls to the methods and functions provided by the relevant interfaces. Unfortunately, it’s just the way things work.
What if you could make this simpler? If you are familiar with SQL, wouldn’t it be great if you could simply leverage your SQL skills and interact with D365 API as you would with a regular DBMS (database management system), or better yet, if you could just have Dynamics 365 synchronize the database using Microsoft SQL Server Management Studio…
Well, we have wonderful news for you!
Connect to D365 Online From a Local DBMS or Any Client Application
We welcome you to our D365 Database Sync Solution! Being entirely based on Connecting Software’s integration platform Connect Bridge, it is custom made to sync on-premises databases to the D365 cloud. Amazing, right?
If you’re wondering how it works, 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) this solution 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.
Do you know what this means? You can connect and insert data into D365 online, from your local DBMS or any client application!
This is especially handy in the case of companies migrating from Dynamics CRM on-prem to D365 online, that have on-prem SQL databases or applications connected to Dynamics. Fortunately, these connections can be kept by implementing Connect Bridge. This is exactly what an Italian supermarket chain did, as you can read in this case study.
It is important to note that if you wish to connect to other Dynamics 365 solutions, like Dynamics 365 Business Central or Finance, this also works! We have your back…at the end of the article.
Now, it’s time to see how this works in practice. You can go for the free trial and try it yourself, you can ask our experts to show you around on a free demo or you can simply read on!
From any Database to Dynamics 365 Online
The famous Adventure Works DW (Datawarehouse) database from Microsoft (download available at the end), is going to serve as our base for this demonstration. We’ll showcase how seamless it is to integrate your existing processes with Microsoft Dynamics 365 Online using the Connect Bridge Platform.
In the following procedure, we will use SSMS (SQL Server Management Studio) to manage your SQL Server Database:
1 - Once you set up the AdventureWorks database, you will see the schema of the database in the Object Explorer window, as in the following image.
2 – We will now create the Linked Server. For that we will follow the steps at this video tutorial.
Please note the creation of Linked Server connections to any target system, by means of connectors, is possible because we are using the Connect Bridge Platform. There are 400+ connectors readily available to our users worldwide. For this example, we specifically use the Dynamics 365 connector.
Connecting Software’s selection of connectors in the CRM, ERP and accounting areas
3 – Once the Linked Server is set up, it will look as if you are accessing the back-end database of Dynamics in SSMS. In the image below you can see it with the name “D365_CRM”.
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 regular database. But this is, in fact, an abstraction with API calls running in the background to make it this simple in the front end.
Using this 3-step procedure will allow you to create any kind of integration and/or customize existing processes. Let’s take a look at what users around the world use this for.
Applicability of the On-prem to D365 Cloud Sync Solution
In case you are wondering why you would ever need to do this in the first place, here are two common scenarios that may hit close to home:
Scenario 1
The company you work for still uses Microsoft Dynamics CRM on-premises, and they are currently in the process of migrating to Microsoft Dynamics 365 Online. They have several integrations between from their SQL server databases to their Microsoft Dynamics CRM on-premises.
Problem is, you can’t connect with Microsoft Dynamics 365 Online database directly, and as a result, you start to wonder whether:
- You will have to create, from scratch, all the processes using the Dynamics CRM SDK from Microsoft;
- You will have to create, from scratch, all of the processes using the Web API;
- You will have to learn C# in order to do all this;
Scenario 2
The company you work for has a custom-made ERP System with a SQL Server database as back-end. They decide to use Microsoft Dynamics 365 CRM as their own Customer Relationship Management System.
Then, they would like to have the ability to populate the CRM with existing data from their ERP System in an automated way and keep both sides consistent.
Doing this process manually is practically impossible since there are tens of thousands of records that need to be identical on both sides, including accounts, contacts, and products.
However, the company’s IT team does not have much experience with the necessary programming languages and frameworks, such as C#, REST services or the CRM API. They do have experience with SQL, but can that be of use? The manager wonders if they:
- Will have to hire a consultancy company to perform this service for them.
- Will depend on the said consultancy company to maintain and add new features as their business expands.
Thankfully, the answer to all these hypothetical questions is no, and hopefully, at this point, the use of the example above is ringing a bell.
With Connect Bridge, you can leverage your existing skills in a efficient way AND keep your existing software, databases and processes.
But you don’t have to take our word for it. Check out the step-by-step example below, or, even better, ask for your free trial and give it a try yourself.
Populating Dynamics 365 Online Accounts from Adventure Works Database Table DimCustomer
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
Testing
1 - 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';
3 new accounts were added to Dynamics 365 without touching Dynamics 365. Cool, right?
But hold on a second, in the table DimCustomer, the field CustomerAlternateKey has a strange number. They’re not 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 for returning 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.
Sweet!
In Conclusion
These are simple examples, but they do demonstrate how this works in practice. It is a scalable and powerful solution that allows you to seamlessly:
- Centralize everything that was previously connected to your on-prem Dynamics.
- Move to the online version of Dynamics and keep the existing integrations working normally.
And, not least important:
- There is no need to learn any new technology or programming language! It’s as easy as it gets.
- You can enhance the process by modifying the triggers.
Also, if you immediately scrolled down to find out which other Dynamics 365 solutions you can easily connect to using Connect Bridge, this is that part!
They are:
- Dynamics 365 Finance and Operations;
- Dynamics 365 Business Central;
- Dynamics 365 Customer Engagement.
Such a treat!
If you have any comments, or suggestions, write them in the space below and we’ll respond ASAP.
Why Customers use Connect Bridge
Resource links
Connect Bridge Free trial
Connect Bridge Learn more
Microsoft Fast Track
Microsoft Fast Track for Dynamics 365
“Microsoft Dynamics CRM on-premises to Dynamics 365 Online Migration” document
Adventure Works database
Microsoft Dynamics 365 official website
Article last updated on: March 28, 2022
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!
Comments 6
Thanks for the info!
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?
Author
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
Interusting Post I like it
And Thanks!
Would this allow me to migrate Cases from CRM 2016 on prem to Dyn365 on cloud and still be able to have them connected to Contacts on prem? Contact lookups would be bringing our agents back to on prem version, but Cases would be updated on Cloud.
Author
Yes, with Connect Bridge you could migrate Cases from CRM 2016 on-prem to Dynamics 365 on the cloud. A member of our team will reach out to you via email in case you want further details