• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Blog
  • Power Platform
    • Power Apps
    • Power Automate
    • Power BI
    • Power Virtual Agents
  • Dynamics 365
  • Azure
  • About Me
Joe Gill Logo

Joe Gill

Microsoft MVP - Power Platform Consultant

Home » Azure » Flow to Synch Dynamics 365 with an On Premise Database

Flow to Synch Dynamics 365 with an On Premise Database

19th November 2018 by Joe Gill

Up until recently synchronizing data in in the cloud with an on premise database was a major challenge which typically involved writing code. With the Digital Transformation revolution and the availability of a range of integration offerings in Azure this is no longer the case. Here is a sample Microsoft Flow which demonstrate how the creation of a contact record in Dynamics triggers the insertion of a record in an on-premise database.

The Flow itself only comprises of a trigger and an action and  the reason it works is because I have installed a Data Gateway on an on-premise server to porvide access to my on premise SQL Servern premise.  A Data Gateway provides a secure bridge between your on premise data sources and cloud services uses the Azure Service Bus Relay service under the hoods. I wiill cover installing the Data Gateway in another post.

Flow to Synch Dynamics 365 with an On Premise Database Joe Gill Dynamics 365 Consultant

Create a new Microsoft Flow and select create from blank and  select the Dynamics “When a record is created” trigger. Add your Dynamics connection details and select Contacts as the entity that triggers the flow.

Flow to Synch Dynamics 365 with an On Premise Database Joe Gill Dynamics 365 Consultant

Once the flow trigger is configured add an action selecting SQL Server and insert record as shown.

Flow to Synch Dynamics 365 with an On Premise Database Joe Gill Dynamics 365 Consultant

The next step is to configure your SQL Server connection and select the Data Gateway that you want to use. You will need to have your Data Gateway and SQL Server set up before you configure this action in Flow. In my example SQL server is running on the same server as the Data Gateway so I can simply use . as the SQL server name.  I had already created a database called demodb with a table called  Contact. I also created a SQL Server account call DG that has insert permissions on the contact table. It is just a matter of filling in these details and giving the connection a name.

Flow to Synch Dynamics 365 with an On Premise Database Joe Gill Dynamics 365 ConsultantOnce the connection is configured Flow will connect to the database and allow you to configure the insert action using the Dynamic content from the Dynamics contact entity.

Flow to Synch Dynamics 365 with an On Premise Database Joe Gill Dynamics 365 Consultant

Save your flow and now everytime you enter a contact record in your Dynamics 365 instance it will insert a record into your on-premise SQL server contact table.

Flow to Synch Dynamics 365 with an On Premise Database Joe Gill Dynamics 365 Consultant

 

Just a few comments on this approach. This is a tightly coupled solution and if the Data Gateway or SQL Sever is not running then your Flow will fail. You need to consider how to handle this possibility. The other point is that the Data Gateway reside in your Azure tenancy so this techique is not suitable for synchronizing cloud data with a partner’s on premise database. I will do another post on using Queues as an alternative to synchronize between cloud and on premise data sources.

I did this example in Flow but you could just as easily could  have created it in Logic Apps with the added benefits it provides.

Share This On Social:
  • Tweet
  • Flow to Synch Dynamics 365 with an On Premise Database Joe Gill Dynamics 365 Consultant

Filed Under: Azure, Dynamics 365, Integration, Power Automate Tagged With: dynamics 365, Flow, Logic Apps

Primary Sidebar

Popular Categories

  • Power Platform
    • Power Apps
    • Power Automate
    • Power Virtual Agents
  • Azure
    • Logic Apps
  • Dynamics 365
  • .NET
  • AI
  • SQL

More to See

Power Platform Requests – Base Request Capacity

17th December 2020 By Joe Gill

Power Automate Desktop

Power Automate Desktop – UI Flow

26th November 2020 By Joe Gill

Tweets

Footer

Joe Gill

Microsoft Business Applications MVP – Power Platform, Dynamics 365 and Azure.

An architect with over twenty years experience designing and developing technology solutions. Specializing in the Microsoft technology stack including Power Platform, Dynamics 365 and Azure. Microsoft MVP Profile

Connect on Social

Useful Links

  • Home
  • Blog
  • About Joe Gill
  • Power Platform
  • Dynamics 365
  • Azure

Featured Posts

Power Platform Requests – Base Request Capacity

Power Automate Desktop – UI Flow

MB-600 – Solution Architect – Supportability

AI Builder – Form Processing Layouts

© 2021 · Joe Gill