How to Build a Reliable Live Data Replication Architecture (Part 2): Implementing the Outbox Pattern

How to leverage persistent layers for atomic transactions with guaranteed delivery

Photo by Daria Nepriakhina on Unsplash

This article is a two-part series about how to engineer live data replication architectures using SQL triggers, and the outbox pattern. You can read part one here.

Let’s not sugarcoat it, software engineering as a profession is mostly about data plumbing — there I said it. I am reiterating a statement by Karl Hughes in one of his articles titled, “The Bulk of Software Engineering is Just Plumbing”, but the statement is true isn’t it?

Most of us use tools that are already provided to us, the advent of new open-source technologies has also solved a lot of the optimization for us. It is also rare for developers to design new patterns as most sophisticated design patterns have already been conceived years ago, but this isn’t to say that the development of software has plateaued, rather it just means that more software engineers are focused on the product itself.

One of the most important aspects of data plumbing is being able to guarantee delivery of data from point A to point B. This might sound simple at first, write an app to send a few API calls here and there, and voila! You have successfully made a data pipeline. But it becomes more complicated when you want to have proof of a successful transaction. After all, anything that can go wrong will go wrong — at some point...

Overview From The Previous Article

Image by Author

The diagram above is a visualization of the architecture of our data flow. Our goal is to achieve live data replication by copying events into the outbox table using SQL triggers. Then, an event scheduler will read the table periodically and will publish the event to RabbitMQ. There will be a consumer listening for events from RabbitMQ that will update data on the target table.

This tutorial will be done in C# and will use ASP.NET Core as the base framework.

The Outbox Pattern

Outbox pattern is a design pattern used when atomicity and guaranteed message delivery are required. The outbox pattern is based on the guaranteed delivery pattern and it requires a persistence layer. The persistence layer will act as an “outbox” to store messages, and the corresponding status of the message, hence the name.

The reason behind an “outbox” is to track the status of each message sent and are successfully processed by the event processor, this is how the outbox pattern can help guarantee message delivery. In some aspects, other than being used as a guarantor the persistence layer can also be used for data audits.

If you are looking to have a more detailed explanation of the outbox pattern, Kamil Grzybek wrote a definitive article regarding this topic.

The standard outbox pattern usually has the following sequence:

Image by Author

Each message is saved inside the outbox table, think of it as an “event bus” of sorts. The event processor will read periodically from the outbox table, each event that is processed will be marked on the outbox table and because you are making a live data replication architecture the event processor would modify data inside the target table(s).

The outbox table can either be on the same or in a different database from the target table. If it is on the same database you can wrap the process in a transaction scope to guarantee the deliverability of the event, if the database of both the outbox and receiver tables are different, then you would be applying distributed transactions.

However, the original scheme has a few drawbacks. By only having one processor to handle all the events, it becomes harder to scale. So, to solve this problem you must implement a “load balancing” algorithm to easily add processors and scale.

Image by Author

You can utilize a message broker or an event bus to distribute the events to multiple processors. In this case, you will be using RabbitMq as our event bus because RabbitMq already had a built-in load balancer algorithm.

The main difference lies in the sequences between the outbox table and the event processor, because the event would be load balanced to multiple processors there needs to be a standalone scheduler to read and send events to RabbitMq for it to be distributed to a processor.

The rest of the sequence would follow the original procedure.

Architecting the Outbox Pattern in C#

Image by Author

According to the sequence diagram, you would need:

1. A standalone scheduler to read events and send them to the event bus.
2. The event model.
3. An event processor.

Creating the scheduler using Cronos

To trigger events from the outbox you need to make a simple scheduler to read events periodically. Ideally, the scheduler should read for new events every 30–60 seconds. The short timespan would make the scheduler somewhat “live” while leaving enough time for the scheduler to send a few thousand messages to the RabbitMq message broker.

First, you would need to download a third-party library called Cronos to help us make periodic events. Cronos is a fully-featured .NET library for parsing Cron expressions and calculating next occurrences according to the local time zones.

I have made a simple base class implement schedulers using Cronos:

Code snippet by Author

After you add the BaseCronJob class to make any schedulers you will only need to extend it:

Code snippet by Author

Notice the CronExpression because you are accepting seconds on the cron expression 0 * * * * * means that you schedule it to run every minute. Next, you need to register the scheduler as a hosted service on IHostBuilder on the Program.cs.

The scheduler will be registered as a hosted service and will run at the start of the program. Now all that is left to do in the scheduler is to add code to get events and send them to RabbitMq.

Making the event model

To send and receive messages from RabbitMq you will be using MassTransit. MassTransit is a third-party library made for quick and easy solutions to integrate all kinds of event busses on your application. It really is a time-saver in terms of features and has a quite practical approach to solutions. Furthermore, the documentation on this library is fantastic, to say the least.

In MassTransit, you would send messages in the form of objects. To differentiate between messages in the same queue, each object would be identified by its namespace and class name.

Each message type should have a unique namespace and class name. This is to avoid confusion and to make the messages more predictable. I have found the easiest way to make unique objects is by making them on a separate project to then be referenced by both scheduler and consumer projects. This way, you don’t need to worry about object mismatch.

There are no specific rules for sending objects in RabbitMq, you only need to have the same namespace, class name, and the object would be in the form of a model. For example:

Code snippet by Author

Connecting to RabbitMq using MassTransit

Sending messages using MassTransit is quite simple, first, you would need to create a RabbitMq instance:

Code snippet by Author

In the snippet above, RabbitMq is accessed from the MassTransit bus factory. Don’t forget to start the RabbitMq instance after you created it.

Then to send messages you would need to initialize the instance and get the endpoint. After receiving the endpoint you would need to call the .Send() method:

Code snippet by Author

If you no longer need to use the endpoint anymore, you should close the connection using the .Stop() method on the instance Bus.

Consuming RabbitMq events using MassTransit

Handling events from RabbitMq is quite straightforward while using MassTransit. You would need two components:

1. An event consumer to handle the events.
2. Event routing.

Writing event consumers is actually simple, you just need to create a class that extends the IConsumer interface.

Code snippet by Author

The template above is sufficient to process events and you only need to add code to process data. You can access the message data from the context.Message property and the example assumes that there would be a MessageId property within the EventModel.

Next, routing is implemented in the main entry point, more specifically the Program.cs file:

Code snippet by Author

In MassTransit, you first have to register the consumer using the AddConsumer function and continue to manually route each event endpoint to the corresponding consumers. In this case, you are routing the event_name endpoint to the EventProcessor consumer.

Don’t forget to add services.AddMassTransitHostedService(); at the end of the configuration. This is the line that will initiate all the consumers as a hosted service, thus not having this on your Program.cs file is a fatal mistake.

Conclusion

To summarize, building reliable live data replication architectures is no easy feat added with the risk of potential data loss or inaccuracies you have to tread carefully in how you approach our solution.

In this case, the solution uses the outbox pattern to guarantee deliveries. There are a few components to our solution, mainly:

• SQL Server for SQL triggers.
• Background scheduler for reading the outbox table periodically.
• RabbitMq acting as the event bus.
• An event consumer to process incoming events.

Software engineering as a profession is mostly about data plumbing, in the end, it would be best to have the most resilient solution possible.