Designing Integration between systems

Lots of new developers ask me, what points we should keep in mind when we are designing or proposing for new Integration work between two systems. So I thought to write some major aspects which I keep in mind while designing the integration piece. The all below points are as per my experience in some of my Dynamics CRM projects with SAP ECC and Dynamics AX (Operations).
Below are the points which I keep in mind to identify the requirements:

1) Asynchronous vs Synchronous: To decide on this point you need to understand the data requirements. For example, when you want to bring master data (accounts) from SAP ECC or Dynamics AX (Operations) to Dynamics CRM, you can go for the asynchronous approach, as the user can wait in this condition before starting to use these account in CRM for any business transactions.
However, at the same time if the requirement is to do a credit check of an Account against some quote amount before converting an Opportunity to Order booking you might require to do a credit check from ERP system. In this case, we can do a real-time call from CRM form to the ERP system to get credit balance for that account.

2) Volume: Need to identify how many updates or new records we are talking about per day. The volume of data sync can help you to identify the Frequency of batch.

3) Frequency: Based on the volume of data and your product usage timings by your end users you can decide the frequency of the batch in asynchronous sync. I generally consider the time zone of the users, for example, if in your CRM you have user base from Canada, Singapore and Australian then based on their work timings you would like sync the data before their start of the day.

4) Events: While taking of the requirements please always confirm the events on which you would like to do transactions. Generally, I have seen designer only thinks about create and update events of the record. However, we should also think about other events for example what will happen if some data will be deleted in the source ERP system which already exists in CRM, as it’s not a good idea to delete a record in CRM if it's already been used with some business transactions. Also, think about another status of the records such as Activation and Deactivation of the record.

5) Filter Criteria of data to be sync: Please discuss with business what data criteria they are interested to bring from other systems.

6) Mapping of attributes (data types and length): Once the criteria of data is decided, the technical team[CRM, middleware (Mulesoft), ERP system] should sit together and mull on the data types and length while mapping of the attributes.

7) Data transformation: Technical team should also think does it require any transformation of data before syncing the data to the target system. This step can help us to analyse and understand the complexity of the integration.

8) Ownership of the records: While the transformation of data also considers who can see the data in the target instance. You might like to set some Business Unit team as owner of the record, so that that the particular business unit users only can see their relevant data.

9) Error Handling and Monitoring mechanism: This is one of the most important steps to understand and design the integration architecture. As once the application is going live how the support team will resolve the issue or conflict between different source and target system teams. The technical team needs to think deep before implementing it.

10) Notification: It’s always a good idea in case any failure or any issue we should trigger a notification email to a Support Group email id. So that support team can take corrective measure accordingly.

11) Conflict resolution troubleshooting: As we know in an integration multiple parties can be involved to complete the business transaction. If after deployment of the integration application any issue is coming there should be predefined steps defined in a document so that the support team can take action accordingly.

12) First-time sync: If integration application is going live first time, always double think about the data load for the first time sync. You need to keep in mind how to handle first-time Full Sync and then after that the incremental sync.

13) Batch processing: For any good integration design always think, will you hit database hundred times to do a hundred transactions in a batch or you will hit only one time to do hundred database transactions in one go. Dynamics CRM gives deep functionality to implement batch transactions. You can read further about ExecuteMultipleRequest vs ExecuteTransactionRequest in MS CRM to understand it better.

14) Alternate Key (Primary Key): It’s obvious when we do integration between two systems we might require Alternate Key perform the transaction. Always define the primary key of the record which is easy to understand by both systems. For example, we have an account entity in CRM, by default CRM create GUID as the primary key of the record when we create a record. Performing integration transaction with GUID might be not the best way to integrate the systems. So Microsoft Dynamics CRM gives alternate key feature in CRM, so with the help of this feature, you can create an Account Number as the alternate key for the integration transactions, which is readable and easy to understand by both systems.

15) Which service account you will use for transactions: Always consider this point which type of user you will use for the transactions. These days to access MS CRM WebAPI we can use Azure user with Client ID and Client Secret. To use Azure user you need to register the application in Azure first.

16) One way or Two way: To complete any integration transaction it’s always a good idea to have an acknowledgement for each transaction.

17) Connection Pooling or Cache: Creating a network connection to a database server is (relatively) expensive. Especially when we do bulk operations it can impact timeout issues or other performance issues.

Using a connection/statement pool, we can reuse existing connections/prepared statements, avoiding the cost of initiating a connection, parsing SQL etc.

Kindly comment your points, if I am missing something!!