Data backup/slave node with replicated data, to be writeable while master is offline

Question

I'm developing and designing a system which runs mostly on intranet of client companies. The system consists of one main database which is MariDB, for now one web-application as system management and data output/analysis/export (which is hosted on same HW with the main DB), and multiple possible little applications that are spread across multiple points within the area of the company, maybe even in another area. These little applications for now directly connect to the main database.

Problem occurs when the main DB goes offline, because we need these little application to be running. One solution would be to setup a replicated slave DB at every point of these application to use it as backup. But since these applications also generate some records or data, they need to be stored somewhere (and would be later synchronized to the main DB). And because the application needs to also work with the data that its generating - for example it creates a new person and saves a record about some safety training I would need them to be written to the backup DB, so I dont have to write a complicated DB access service.

Some additional info:

• The whole network is intranet of the company most of the time
• All PC run Windows
• All my DB entity IDs are "Snowflake ID", it means I can generate ID independently from all other little applications when generating a new records without collisions
• For now and for simplicity all these "little application" connect directly to the DB, but I would like them later to be communicating only with the web-api server. Which in this case would be installed on every point with the eventual backup DB.

The incomplete solution I came up with is that every data change request would first go through "data-service" which in this case would be the web server. This data-service would run on the main server, but also on every other point, where the backup DB is needed. The step-by-step flow would look like this:

1. I have a point A (Little application, Data-service server, backup replicated DB server)
2. Application request a record save of some data, so it sends the request to the data-service server.
3. The data-service would first check if main server is online, if it is then send the request to the main server and replication would make sure the record is stored everywhere else too.
4. If main server is offline, it would serialize and store the request for later to send when main server is back online. And it would also store the record in the backup DB, so the application can continue work with the saved data.
5. Problem with this is that is would break the MariaDB replication.

I'm not really sure how to solve this design problem.

Answer 1

You're building a distributed system – which is inherently complicated.

Distributed data stores will struggle with the CAP theorem. Of consistency, availability, and partition tolerance, pick any two. Consider what happens if the clients lose connection to the central server.

• If all writes go to the central server first you have consistency, but give up availability.
• If writes can be cached locally, you will have availability, but give up consistency – different clients might see different states.

Both of these can be valid.

• A centralized architecture can work very well. You're on an intranet, which can be considered comparatively reliable. Using techniques such as blue–green deployments, you can perform zero-downtime maintenance on the server and can achieve a high degree of fault tolerance. The main risk are misconfigurations.

• “Eventual consistency” can also work very well, but only if this is compatible with the business problem. Eventual consistency means that the databases will eventually sync up with each other and agree on a common state. But this can be quite difficult in practice. Blindly repeating writes might cause data loss, you should also consider some conflict resolution approach.

There are a couple of approaches for handling conflicts.

• Manual conflict resolution, which requires representing conflicting states in your data model and providing a UI for viewing and resolving conflicts. Essentially, conflict resolution must be addressed on the business level.

• Automatic but non-deterministic conflict resolution. Upon conflicts, one or the other state is used, without clear predictable rules. This is going to lead to bugs, though.

• Deterministic conflict resolution. For example, if we can assign a total order for all updates (such as a global timestamp or random ID), then we can use strategies such as “highest write wins”. Since some writes will be dropped, this might be experienced as data loss.

• Conflict-Free Replicated Datatypes (CRDTs). Some writes can be resolved in a deterministic manner regardless of their order. For example, multiple “add x to a counter” writes will produce the same result when applied atomically, without ever leading to conflicts. But CRDTs only work with specific operations on specific data structures, and cannot be a general-purpose strategy for avoiding conflicts.

• Event Sourcing. Instead of having a representation of the current state as the source of truth, a stream of events is stored. The event stream can be processed to derive the currently known state. As new past events become known, the current known state can be recalculated. The rules for applying events to a state must encode conflict resolution semantics, for example by skipping states that are invalid in the current state (like changing the name of a user that doesn't yet exist).

It sounds like your data service would re-attempt writes to the central DB when the connection is re-established. If multiple clients can create writes affecting the same objects, this could lead to a non-deterministic state (whoever retries the write last, wins).

You are concerned that writing to the local database replica will break the database's built-in replication. Then, consider not doing that. It could be appropriate to treat the replica as read-only in all circumstances, and to represent pending writes separately within your application – see also the above point that eventual consistency is also a business-level concern.

Given that you'd be building an eventually consistent system anyway, it might be better to skip the database's built-in replication mechanism and to write your own tool that will sync the DBs on request, following the conflict resolution rules of your choice.

Personally, I think all of this sounds super complicated. Building an eventually consistent system might be necessary, but it also might not be worth it.

• What level of consistency is actually required by the business processes? Can there ever be more than one client affecting the same records? In case the central database goes down, would it be necessary to inform users which changes have been committed and which changes are still pending (and might be lost during resolution)?

• What uptime is actually required for the system? Considering the development effort for such solutions, wouldn't it be cheaper to build a redundant centralized system? This may or may not require additional hardware, depending on availability goals.