how can I ensure database connectivity on a spotty network? (and allow for entirely offline connectivity)
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:


https://particular.net/blog/the-network-is-reliable 
https://www.enterpriseintegrationpatterns.com/patterns/messaging/Introduction.html