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:
- I have a point A (Little application, Data-service server, backup replicated DB server)
- Application request a record save of some data, so it sends the request to the data-service server.
- 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.
- 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.
- Problem with this is that is would break the MariaDB replication.
I'm not really sure how to solve this design problem.