SQLite backup using Windows DFS Replication

Question

I have an application that use SQLite for storage, and I'm wondering whether it is safe to use Windows DFS Replication to backup the database file to a second server which has a cold standby instance of the application installed.

Potentially relevant details:

1. Although DFS supports two-way replication, in this case it is only the master DB file that is written to, so the replication is effectively one-way.
2. The master DB file is located on the same server as the process that is writing to it.
3. Currently SQLite is configured to use the standard Rollback Journal, but I could switch to Write-Ahead Log if necessary.

If DFS locks the master DB file during replication then I think this approach could work as long as the lock isn't held for too long. However I can't find sufficient information on how DFS is implemented.

UPDATE: I've implemented this in a test environment, and have had it running for several days. During that time I have not encountered any problems, so I am tempted to go with this solution.

Answer 1

Considering that DFS Replication is oriented towards files and folders:

DFS Replication is an efficient, multiple-master replication engine that you can use to keep folders synchronized between servers across limited bandwidth network connections.

I would probably try to avoid it if you care about consistency and keeping all your data, as stated by the SQLite backup documentation:

Historically, backups (copies) of SQLite databases have been created using the following method:

1. Establish a shared lock on the database file using the SQLite API (i.e. the shell tool).
2. Copy the database file using an external tool (for example the unix 'cp' utility or the DOS 'copy' command).
3. Relinquish the shared lock on the database file obtained in step 1.

This procedure works well in many scenarios and is usually very fast. However, this technique has the following shortcomings:

1. Any database clients wishing to write to the database file while a backup is being created must wait until the shared lock is relinquished.
2. It cannot be used to copy data to or from in-memory databases.
3. If a power failure or operating system failure occurs while copying the database file the backup database may be corrupted following system recovery.

In the case of DFS, it wouldn't even lock the database prior to copying.

I think your best bet would be to use some kind of hot replication, you might want to use the SQLite Online Backup API, you could check this tutorial on creating a hot backup with the Online Backup API.

Or if you want something simpler, you might try with SymmetricDS, an open source database replication system, compatible with SQLite.

There are other options (like litereplicator.io), but this one went closed source and is limited to old SQLite versions and ~50MB size databases

ps. I would probably move away from SQLite if you really need HA, replication or this kind of features. Depending on your programming language of choice, most probably, you already have the DB layer abstracted and you could use MySQL or PosgreSQL.