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.
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.
Once the flow trigger is configured add an action selecting SQL Server and insert record as shown.
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.
Once 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.
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.
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.
