The Horror of finding the right database! Part 2

If you haven't read the first part: Here is Part 1!

Btw: This topic is not new - Here are two blog posts from 2020:

• Is a database just a data storage?
• The database on network file problem

Perhaps we have special needs, but I don't think so.

The simplest database would certainly be SQLite in Journal mode. Although it should theoretically work with locking and file modes, there are many warnings against using SQLite or other file-based databases in a multi-user network environment.

So, how do we work with a server without having a real server?

Our application could communicate with other applications on the network and coordinate through a communication channel to determine which PC has access to the database at any given time. This would work, but it would be very inefficient. Each PC would need to request access, open the database, perform its task, and then close it before releasing access for the next PC.

As a proof of concept, we could write our own file to the hard drive, but this might lead to the same problems. Additionally, antivirus software tends to dislike frequent file writes in short intervals.

Encryption is also a key requirement for our database. Unfortunately, the SQLite’s encryption feature has become a real hurdle, with a $2,000/year licensing fee. So, you're either stuck with an old version or forced to pay the fee, which is not a viable option for small applications.

Another option would be Firebird. A Firebird database can be accessed either via an embedded driver or a server. The first PC that starts could access the database directly or launch the server instance locally. When a second PC starts, it could "ask" the other PCs on the network if a server instance is already running and then use that server.

We could attempt to establish a connection from each PC to the server instance on the other PCs, read a table where the active server logs its activity, and if no instance is active, we can start the server and update the table accordingly.

In theory, this should work! If the respective PC is already turned off, we would just need to wait for the connection to time out, and then start the server instance ourselves - if we are faster than another PC on the network. Another PC might have already taken over the job.

Without having programmed this functionality yet, I already foresee numerous problems with this system.

No matter how we approach it, we need communication between the PCs. Of course, we can build these functions into our application. With each application shutdown, another PC might need to "take over." For example, if I shut down my application, the communication would be interrupted, and the application on another PC would need to take control, coordinating access between PC 2 and PC 3. At this point, things start to get complicated.

Moreover, the application could crash, or the user could simply shut down the PC as mentioned above.

Starting or stopping a server instance, or applying updates, may also require administrator rights, and you wouldn’t want to constantly prompt the user for those permissions.

The conclusion of these considerations is that a Windows System Service must be installed on each PC to handle these tasks. This way, our application can simply connect to the Service-Application on the correct port, which will act as a mediator.

First, we need to develop a Windows System Service capable of maintaining the information on which PC the current server is running and how it can be reached. The service handles this task in its own thread and can respond very quickly if needed. For example, each system service receives a notification when Windows is shutting down! The service can immediately notify the other system services and, based on a priority list, instruct the next PC to start a server instance.

Within a few milliseconds, a new server will be ready to take over the work.

To establish our connection to the database, we simply need to send a query to our system service, which will return the IP and port for the server instance. Since our system service only provides the correct "login details," all database drivers work normally.

However, communication between FireDAC (for example) and the database happens without our intervention. So, if we want to execute a longer database operation, we need to inform the system service currently holding the server instance and ensure that the PC isn’t shut down until the operation is complete.

Even though communication with the database already happens via TCP/IP, FireDAC handles this communication in the background. We definitely don’t want to analyze that traffic.

A query wrapper or a wrapper for the FDConnection could handle this task.

If we’re already "announcing" the database access via TCP/IP, do we still need a real server, or can we switch back to a flat-file database?

Stay tuned for what's coming next.