If you are like me and working in an industry where there are lots of legacy systems involved and your company decides to shift to cloud based solutions like CRM or ERP etc. then you might find my experience below somewhat useful to you, as it’s always being said learn from others experience.
So let me put the use case that I had to deal with so that it will be easier to understand the implementation that we did, I am in no way saying that it is the best implementation but again the major reason I am writing this blog is to get the comments and suggestions of all the Mule Gurus out there.
Use case:
We have legacy system built with Oracle DB using Oracle forms to create the applications and lots and lots of stored procedures in DB and it’s been in use for over 17 years now with no major upgrades and changes but lot of development changes over these 17 years that had made the system close to breakpoint and almost impossible to implement something new. So the company decided to move to CRM (Salesforce) and we need to transfer data to SF from our legacy DB but we can’t create or make any triggers on our DB to send real-time data to SF during the transition period from legacy to SF.
Solution:
So we decided to use mule Poll to poll our database and get the records in bulk and send them to SF using salesforce connector of mule.
I am assuming that we all are clear about Polling in general if not please refer to references in the end. Also if you are not clear with mule polling implementation there are few references at bottom.
Sounds simple enough isn’t it but wait there are few things to consider.
- What is the optimum timing of poll frequency of your polls
- How many threads of each poll you want to have, how many Active or inactive you want to keep in pool.
- How many polls we can write before we break the Object store and queue store used by mule to maintain your polling’s
- What is the impact on server file system if you use Watermark values of object store
- How many records we can fetch in one query from DB.
- How many records we can actually send in bulk to Sales-force using SFDC
These are few if not all the consideration you have to do before implementing.
The major part of Polling is WATERMARK of polling and how mule implements the this watermark in server.
Polling for Updates using Watermarks
“Rather than polling a resource for all its data with every call, you may want to acquire only the data that has been newly created or updated since the last call. To acquire only new or updated data, you need to keep a persistent record of either the item that was last processed, or the time at which your flow last polled the resource. In the context of Mule flows, this persistent record is called a watermark “
To achieve the persistency of watermark, Mule ESB will store the watermarks in object-store of runtime directory of project in ESB server, depending on the type of object-store you have implemented you may have SimpleMemoryObjectStore or TextFileObjectStore it can be configured like below
<objectstore:config name=”Push_SFDC_ObjectStore__Configuration”
objectStore-ref=”myListableObjectStore” doc:name=”ObjectStore: Configuration” />
This is Simple Memory object store sample
<spring:beans>
<spring:bean id=”myListableObjectStore”
class=”org.mule.util.store.SimpleMemoryObjectStore” />
</spring:beans>
Text File object store sample
<spring:beans>
<spring:bean id=”myListableObjectStore” class=”org.mule.util.store.TextFileObjectStore”>
<spring:property name=”entryTTL” value=”300″ />
<spring:property name=”expirationInterval” value=”300″ />
<spring:property name=”maxEntries” value=”5″ />
<spring:property name=”name” value=”AXA_CRM_OBJECT_STORE” />
</spring:bean>
</spring:beans>
For any kind of object store mule ESB creates files in server and if the frequency of your polls are not carefully configured than you may run into file storage issue on server, for example if you are running your poll every 10 second with multiple threads running and your flow takes more than 10 seconds to send data to SF then a new object-store entry is made to persist the water-mark value for each trigger of flow and we will end up with too many files in server objectstore.
To set these values we have consider how many records we are fetching from DB, since SF has limit of 200 records that you can send in one bulk, so if you are fetching 2000 records from DB than one batch will call SF 10 times to transfer these 2000 records so if your flow takes 5 seconds to process 200 records including the network transfer to send data to SF and come back than your complete one poll will take around 50 seconds to completely transfer 2000 records, if our frequency of poll is 10 seconds it means during one batch of poll completes in 50 seconds we are creating 5 more and it keeps going on and on and keeps pilling the object store.
Another issue which will arise is the queue-store, since the frequency and execution time have big gap the queue-store’s will also keep queuing and again you have to deal with many files.
To resolve this it’s always a good idea to fine tune your execution time of flow and the frequency and not to have bigger gap. To manage the Threads you can use batch flow threading function of mule to control how many threads you want to run and how many you want to keep active.
I hope few of the details may help you set up your polling in a better way.
There are few more things we have to consider that is what happens when error occurs while sending data i.e. on our side, what happens when SF gives you error and can’t process your data and types of error’s SF will send you, how to rerun our batch with the watermark value it failed for and logging and recovery.I will try to cover these and exception handling and how to manage the various errors thrown by Salesforce in second edition of blog.
Refrences:
https://docs.mulesoft.com/mule-user-guide/v/3.6/poll-reference#polling-for-updates-using-watermarks
https://docs.mulesoft.com/mule-user-guide/v/3.7/poll-reference
https://docs.mulesoft.com/mule-user-guide/v/3.7/poll-schedulers#fixed-frequency-scheduler