Category: Technology

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

Project Delivery – Back to the Basics

Bring your project back from the Wild Wild West to a state of quality, maintainability, and supportability.

The Fast & Furious

pexels-photo-60230.jpeg

Many of the projects I have worked on had initial similar traits – understaffed, underfunded, and behind schedule. I call this the Wild West West (WWW) phase. Although this stage is chaotic and stressful, I enjoy this stage of a project. There is a tight collaboration between business and technical teams, lots of challenges to solve and everyone is motivated to get sh*t done. The problems in the WWW phase are standards are not followed, the SDLC is out the window, and quality, maintainability, and supportability are afterthoughts. The WWW phase is often fun, challenging and gets the job done but, it is not sustainable and does not produce a quality product.

Regroup & Breath

After the first release is out the door, critical support items are resolved, and things are generally speaking calm, it is time to regroup and breath. It is important as a team to review the processes that are used and ask some key questions:

  • Do we have the fundaments in place?
  • Is everyone on the same page of what the fundamentals are (SOLID principals for example)?
  • Where do we start to mature the delivery process?
  • Is there time allocated in the schedule to mature the model?
  • Do we have the right resources for the team to help mature the process?
  • Do we all agree on what the definition of “done” is?
  • What’s needed to improve quality?

This is an initial set of questions I start with when I in the regroup & breath mode. Maturing the delivery process is all about supportability, maintainability, and having a predictable and repeatable process. Even for the most seasoned teams and IT professionals, it takes diligence, practice, and time to ensure that the core principals are followed.

Back to the Basics

pexels-photo-601177.jpeg

For a project to be successful in the long term, the basics must be at the core of the delivery process. After each deliverable, sprint, or major milestone, I like to review with the team the fundamentals. There are many, but four key aspects stand out:

  • Quality – what is being done, for each discipline, to ensure the team is delivering the highest quality possible. I look for specific items such as functional and technical design sessions, UX reviews, code check-in & reviews, and unit and system testing.
  • Supportability – support is one of those areas that are often forgotten in the mad dash to get things done. Having spent some time in the support world, I try to instill the importance to the delivery team of building a supportable solution. This means at a minimum of having the appropriate documentation, logging, notifications, and training for support personnel.
  • Maintainability – one thing I love about being a developer is that you get to code some really cool stuff. Not only that, but you often get a second chance (refactoring) to make your code better – condense it, make it more efficient and more elegant. Refactoring can be taken too far though – what was readable and easy to follow now looks like Ancient Sand Scripts. My guidance – can someone read the code a year from today and understand what it does? If so, it is probably maintainable code. If not, consider additional whitespace, comments, or a clearer way to write the logic.
  • Repeatability – it is vital to introduce a repeatable process in everything that is done within the delivery process. This starts with the UX design reviews and goes through training the support teams. The process does not have to be fancy, automated or complex, but it does have to be agreed upon, dependable, documented, and followed. As the project and team mature, this is an area that consistently evolves. In my experience, this is always a work in progress.

 

Final Thoughts

finish

The SDLC, design & code reviews, training sessions, and so many other items all contribute to a successful deliverable. The basics are not sexy, but they are the difference between a successful solution that provides value for many years vs. a one-hit wonder. When in doubt, pause for a moment, review the processes in place and get back to the basics.

Until next time.

Chris

Previous Posts

Some of the previous posts I have written are dated but are still relevant and I find helpful. Here are a few:

pexels-photo-891674.jpeg

Never stop learning.

Chris