Is there any type of replication over a WAN (cross-country) that can guarantee no data loss in a disaster? Are we always subject to losing a few seconds or minutes up to the point?
If so, any suggested get-arounds? We are debating using cloud storage or changing or financial app to log one or two of our key transactions onto an off-site server.
It looks like it is possible. SQL Server calls it Database Mirroring, and the key point is to use the "high-safety" mode, which is synchronous. That means that every transaction committed will be committed to both instances before returning as completed.
Do note, this will definitely have an impact on performance. And likely a big impact. Every time you make an update to the database, it is going to be committed locally, then sent to the remote (mirror) server, processed and committed there, then the remote one will return a notification that it has completed the transaction, and only then will the primary DB server return to the client that the transaction was completed. This extra round trip and processing will cause some level of increased latency.
For more information, start with these MSDN articles:
Note: I'm not a SQL Server DBA. Most of my experience is with PostgreSQL and Oracle. There may be a better or alternate way of doing this. If so, hopefully a SQL Server DBA will supply it or correct any mistakes I've made.
I would recommend That on site you should deploy Database Mirroring or Clustering And then over the WAN link Transaction log Replication.
Mirroring over WAN has a very sensitive latency threshold and in the event of poor performance due to network throughput this can cause the mirror to fall out of sync intentionally to protect the database however... in the event of corruption in a database mirror set the corruption would then replicate to all the replicas.
There are of course some deciding factors here such as storage requirements. As you have mentioned you are thinking of cloud storage for disaster recovery although this may help guarantee no data loss this would not satisfy the automatic fail-over of a mission critical database for this application if it needs 100% uptime. Going back a sec If this is a very small database mirroring over a WAN will possible although with encryption is recommended.
However, If there is any secure information in the database it might also be important to see if there are any compliance regulations for the contents of the database that it not be stored on a public cloud but rather a private cloud to satisfy things such as PCI in special cases.
If you need anymore assistance I am more than happy to help or give recommendations. best of luck!