I’m building an application in .net using NServicebus. We have to sync data to a legacy cobol system. We want to be able to process messages in parallel. The issue we have is that message order at the receiving side is important. For now we mainly sync CRUD operations from SQL server to DB2.
What if I first receive a delete and after the delete an add. Maybe it had to be the other way around. I could introduce sequence numbers to detect message order. But what if I receive message 1 and after that 3,4,5. Do I wait for message 2 to process 3,4,5. If I continue, what if message 2 arrives later. Maybe message 2 will never arrive.
So my question is: is there any best prectice to solve this issue. I looks like a typical issue in messaging but I can’t find many answers.
NServiceBus doesn’t provide strict message order guarantees. This is because most transport already don’t support this and additionally, some features like recoverability, delayed delivery and others work the way that a message is reordered (e.g. retried at a later point in time). As you already pointed out, it’s basically impossible to build a reliable data replication mechanism based on those properties.
But more importantly, it’s not recommended to use messaging* as a synchronization mechanism to sync data. Syncing data is very brittle and a simple problem can desync your databases forever while you won’t notice it.
I’m aware that this might not be the answer you’re looking for, but here I go anyway: Have you considered approaches where you keep the data of your new and your legacy system separated instead of syncing those? Syncing data between two systems introduces a huge level of complexity (e.g. dealing with concurrent writes to the same data on both systems) which I’d avoid whenever possible. Messaging is an excellent tool to introduce new functionality via dedicated, autonomous services without rewriting the complete legacy system.
* there is another type of messaging which support strict order in messages which is often called event streaming, stream processing or similar. While these technologies also use asynchronous messages they work fundamentally different in regards to message types, message persistence and message consumption. But these systems can often be used for “log shipping” which can distribute database transaction logs reliably across consumers to create replicas. A typical example for this would be Kafka.
AmazonSQS for example provides FIFO semantics with their FIFO queues. Currently NServiceBus doesn’t support FIFO queues with AmazonSQS. Kevin Sookocheff wrote an excellent article that explains many of the caveats with messaging, exactly-once semantics and total order problems
That being said it is possible though to get some order into your messages by applying the saga pattern.
But for data that has to be kept transactionally in sync that changes frequently the approaches described by Tim tend to work better. Another option is to use the good old ETL tools
