Database storage on TrueNAS and making it HA

We are setting up our first XCP-NG and TrueNAS combos at our datacenter soon. The servers will be used to host some web applications. Original plan was to run the database on the local drives but decided it may be better to go ahead and put them on a remote server in case we need to scale horizontally as traffic increases.

We have a little older hardware so Im looking to run a backup database with a second Truenas. So 2 Dell R730s with SSD’s, 256gb ram, etc (same build outs). My goal is to use TrueNAS 1 as the primary database storage and have it replicate to TrueNAS 2 in case #1 dies for some reason. These will be backed up to another backup server then to offsite. The goal is basically HA database server. All of the TrueNAS systems are on a 10gb SFP switch that is not going to be exposed to the internet (minus the backup server to go offsite).

Anyone see any issues in this plan? I’d like to use NFS but I have read iSCSI is the preferred in this situation.

I would use iSCSI with the dataset async set to “always”. If it were me I’d set up your backups for full, deltas and logs accordingly. That way in case of a failure you can have minimal data loss.

Something like this to your secondary Truenas using the sql agent and SMB.
Full backup: weekly, 1 week retention
Dealt backups: hourly, 1 week retention
Transaction log: minute, 1 week retention