A couple of points to add here (I have been discussing this topic with Alex in a support ticket, as well as a couple of other clients recently):
First of all, just to clarify - we (Shotgun) typically refer to cloud sites as “hosted” (as from our point of view, we do the hosting), and on-prem installations, hosted locally by the client, as “local” sites. Alex is referring to clients using our standard Shotgun cloud-hosted offering, and how to bulletproof against unexpected connection outages.
Also, this may be stating the obvious, but I’ve found that usually this issue becomes most crucial when a studio has created renderfarm dependencies on Shotgun. So there is potentially a read-only data caching approach that could be a reasonable in that case.
We have seen a few very complex bi-directional sync setups over the years, but tend to recommend against going down that path. It can get very tricky to resolve conflicting updates, and dealing with that much data can of course create its own SG performance concerns. If you can stick to a 1-way, read-only cache, containing only the most mission-critical entity/field data, that is updated as infrequently as is practical (daily?), that is likely the best starting point.
Would love to hear what other critical dependencies people have linked to Shotgun - the sort that will be disrupted by even a momentary downtime - and what systems people may have put in place to mitigate the risk there.