Sql-server – how can I ensure database connectivity on a spotty network? (and allow for entirely offline connectivity)

MySQLsql serversqlite

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.

Best Answer

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:

Related Question