My place of work has a pretty spotty network... we have quite regular (though usually brief) outtages.

Not too long ago, my team was logging application data to a SQL Server owned and managed by IT (I am a programmer outside of IT, so I don't control what they do)... some time ago, due to the spottiness of the network and this SQL Server in particular, the applications got changed to point to an instance of MySQL. The data getting stored in MySQL then gets sent to the SQL Server periodically when we can ensure connectivity.

This has improved reliability greatly, however, there are still some annoying blips due to the spotty network issues...

Due to the poor network conditions, and the desire to potentially operate software in an entirely offline state, I have been tasked with looking into local database options...

We are using MySQL currently... I am entirely unaware of a local MySQL option that's simple to configure. I also don't want to be in the business of managing potentially hundreds of MySQL instances... But the data we are recording locally will eventually be migrated to either a MySQL database or SQL Server.

What options are out there for a very lightweight/easy to configure database? What about options for easily migrating this data without a lot of overhead?

Honestly the only thing that comes to mind is to have a bunch of local databases (potentially something like SQLite), and run scripts that talk to the local databases on some given frequency to push the data out to a database server on the network, but this seems like a lot of overhead that we'd need to manage...

I'm afraid this whole setup is feeling quite hacky and I personally feel the responsibility of the data storage should fall on the application itself (the application should know the network is down and wait until it can try to send the data again)... I also feel that more pressue should be put on IT to improve the reliability of the network and the SQL Server... But I am trying to see what options if any are available that might be simple to impement on my end.

  • 3
    This could become quite complicated because you're essentially asking for (in SQL Server) merge replication - it was designed specifically for this. I do not know MySQL, but if the app is storing locally, how will you handle conflicts? It may help to look into Merge Replication since the end server is SQL Server. – Sean Gallardy - Retired User Jul 10 '19 at 0:16
  • You're right. its looking hacky. If you can't get better service, and the network truly is unfixable, you'll need to store data in a way that the merge process will handle conflicts and this needs to be thought about carefully for every data update. Consistency of updates when both sources are updated need to be considered too. – danblack Jul 10 '19 at 2:01
  • Thankfully, no two sources that write to the database will EVER write the same data. Everything is managed on a per machine basis and is all managed by hostnames. So there isn't much handling as far as conflicts. The one issue would obviously be auto-generated keys, but I can avoid that by disabling auto-generated keys on the local machines... or by handling the keys in whatever service I decide to implement to handle this. I'll definitely look into Merge Replication, thanks! – eerick Jul 10 '19 at 21:08

You have come across the first fallacy of distributed computing: the network is reliable. I think no matter how much time and money your company invests in improving the network, there will always be outages.

I recommend designing the application/system such that it does not rely on the network to be up to function correctly. What I think you are asking for is a way for the logging to eventually make it to the centralized SQL Server database. Your proposed "store and forward" mechanism is a great way to accomplish this.

I would look at using a message queuing system such as MSMQ that supports local storage (no network dependency on a central bus/broker). Frameworks such as NServiceBus can assist with this and abstract much of the low layer implementation details. This will help avoid writing the replication, error handling, etc. logic yourself.

From the client app's perspective, instead of writing the log data directly to the remote or local database, it would simply send a WriteLogToDatabaseCommand message. The messaging system running on the local system would persist this in its own storage and then try to forward it to the centralized logging server. It keeps the local copy until it successfully forwards it to the receiver. There would be a receiver process running on the logging server that handles the message by writing the log data to the centralized SQL database.

The same approach can be used for more than just "fire and forget" type of logging information. However, for application data, you would have to design the whole system to work in an asynchronous fashion. In that case, you may also want to research command/query responsibility segregation (CQRS). Since the central state is not known by the remote clients, they must be able to handle that. The updates (Commands) must always succeed, even if that means being more flexible than traditional thinking (e.g. an order is placed but the item is out of stock - still accept the order and say it's on backorder vs. stopping the order from being placed in the first place).

For more information on this approach, I recommend reading:

  • Scott, you've given me some really great information to work with... surprisingly it hadn't even occurred to me to look into any code frameworks that already support functionality similar to what I am looking for. It's definitely going to take me some time to investigate everything and come back with a clean solution for exactly what we are looking for, but you've given me a really great starting point. – eerick Jul 10 '19 at 21:00

A direct answer to your question: "how can i guarantee connectivity on a spotty network?" Is: "You can't".

You may be asking for workarounds or alternatives. You could implement some kind of local db with synchronisation, using advanced product features such as replication. Or you could try and build some sort of distributed application that is happy being semi-connected. Both of these options will be time consuming and expensive, and come with maintenance overhead, and still not provide you will the reliability you need.

The 3rd (and I believe easiest and cheapest) option is to fix your network. A reliable network shouldnt be considered an unreasonable request.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.