Category: Cloud

Hybrid Cloud – Integrating CRM Solutions

Solution for integrating multiple CRM solutions on different domains using Azure

Background

A recent project I worked on involved writing a one-way integration between three CRM solutions across two domains. This project was spawned by an acquisition taking place between two companies. The parent company has two CRM solutions, Salesforce and Oracle. The parent company is in the process of replacing their legacy CRM solution (Oracle) with Salesforce. The objective for “Day One” (the first day the acquisition was final) was to have the Salesforce and Oracle send contact and opportunity records to the acquired companies CRM solution (Dynamics CRM). Over the next six months, the three CRM solutions are scheduled to come together under a single CRM solution. My task was to write a robust, flexible, and supportable integration in roughly a two week period – no pressure.

Solution

This process would have been easier if there was domain trust between the two companies, however; unfortunately, that was not an option for Day One. To get around this, I turned to Microsoft Azure and SQL Server Integration Services (SSIS). The following diagram outlines the data flow for the solution:

Drawing1

The key to the process is to use Microsoft Azure as the communication ground between the two non-trusting domains. In this approach, Microsoft Azure can foster the communication and enforce security ensuring that only the two domains are connecting to the exposed endpoints.
The data flow is relatively straightforward:

  1. From Company A’s CRM systems, the users click a button after selecting a CRM (Oracle or Salesforce) record. On click of the button, the respective system makes a call to an Azure endpoint sending the appropriate CRM record as a JSON request. In this case, the JSON endpoint represents an opportunity record with associated contact information.
  2. The secured endpoint receives the data and inserts it in a managed Azure SQL database table.
  3. Company B hosts and schedules the SSIS package. The SSIS package queries the Azure SQL table on a regular interval to pick up any new or updated records.
  4. The new or updated data from SQL Azure is stored in Company B’s staging database.
  5. Stored procedures in the staging database for used for cross-database joins, data mapping, and other ETL processing needed before sending to the destination CRM system.
  6. The SSIS package calls stored procedures in the staging environment and sends the data to Microsoft Dynamics CRM using KingswaySoft.
  7. When SSIS completes, logging of any failures or successes are sent back to the Azure SQL database. This step is essential for support.

The above steps are a simplification to the process, but the core objective of enabling data transfer from one company to another is outlined.

A few gotchas:

  • To connect to Azure SQL, I recommend using SQL Management Studio 2017. There are limitations to older versions.
  • Unless explicitly known, use a broad range of subnets when securing Azure by IP.
  • When connecting to Azure SQL, you’ll need to grant the IP you are connecting from. You’ll be prompted to log into your domain account. If you do not have permissions to add your IP address, you’ll need to work with your system administrator. See this article for guidance.

Some helpful articles:

 

Chris