TrueNAS as Database Storage: Connecting MSSQL, PostgreSQL, and MySQL the Right Way

Databases are the part of IT where storage mistakes hurt the fastest. A lost redo log, a corrupted page in the system database, or a snapshot without a working restore path — and the business is down. Yet a surprising number of MSSQL, PostgreSQL, and MySQL instances in SMB environments still sit on storage that was never designed for database I/O: a shared VMware or Proxmox datastore, an SMB share for backups, a NAS from the consumer shelf.

This article shows how to configure TrueNAS as proper database storage. It is not a hardware benchmark, but a walk through the architectural decisions that matter in day-to-day operations: iSCSI connectivity, sync=always, correct Slog sizing, snapshots for transactional databases — and the restore practice behind all of it.

Why TrueNAS Makes Sense as Database Storage

Three TrueNAS properties matter for database workloads:

• ZFS as the filesystem: End-to-end checksums, copy-on-write, and atomic transactions align well with ACID databases. Silent data corruption is caught at the block level.
• Separate Zvols per database volume: Data files, transaction logs, and tempdb can each live on their own Zvol with a matching recordsize — significantly harder with classic RAID arrays.
• Snapshots and replication built in: ZFS snapshots are atomic at the block level. Combined with DB-native consistency (VSS for MSSQL, pg_start_backup for PostgreSQL, InnoDB-friendly mechanisms for MySQL), they enable usable point-in-time recovery.

That does not mean TrueNAS replaces every enterprise SAN. For hyper-scaled OLTP with millions of transactions per second, other solutions are the right call. For typical SMB databases — ERP, DMS, ticketing, warehouse management — TrueNAS is an honest and maintainable option.

iSCSI for MSSQL, PostgreSQL, and MySQL

For database workloads, iSCSI is the protocol of choice: block-level access, clean semantics for sync writes, good interaction with ZFS Zvols. The general iSCSI setup is covered in our article on TrueNAS iSCSI storage for Proxmox — the database-specific details follow here.

Zvol and Recordsize Choices for Databases

Recordsize decides how much overhead ZFS creates per database page:

Engine	Data area (volblocksize)	Log area	tempdb / sort
MSSQL	8K or 16K	64K sequential	16K
PostgreSQL	8K (matches default page)	16K or 32K	16K
MySQL / InnoDB	16K (default page)	32K	16K

Consistency between database page size and Zvol volblocksize matters. An 8K page on a 64K Zvol creates read-modify-write cycles and destroys latency. For sequential log Zvols, a larger block size is better, since writes there arrive as large contiguous blocks anyway.

MSSQL: Separate Volumes for Data, Log, and tempdb

For Microsoft SQL Server, splitting into three volumes has proven itself:

• Data volume: mdf/ndf, volblocksize 8K or 16K, compression lz4, sync=always
• Log volume: ldf, volblocksize 64K, sequential write load, sync=always
• tempdb: separate Zvol, volblocksize 16K, often with sync=disabled — tempdb is discarded at restart

This split lets you accelerate the log volume with a Slog device without tempdb traffic bleeding through. Instant file initialization and the right MAXDOP setting also affect storage load significantly.

PostgreSQL: Splitting WAL, pg_data, and Base Tables

PostgreSQL benefits significantly when the write-ahead log (WAL) lives on its own Zvol. WAL traffic is purely sequential and synchronous — exactly where sync=always and a good Slog device tip the scale.

Recommended split:

• pg_data (base tables): volblocksize 8K, lz4, atime=off
• pg_wal (WAL): separate Zvol, volblocksize 16K to 32K, sync=always
• pg_stat_tmp / temp_tablespaces: separate Zvol, optionally sync=disabled

For virtualized PostgreSQL on Proxmox, our article on the Proxmox 3-node cluster with TrueNAS H20 offers a reference setup.

MySQL / InnoDB: Redo Log and Data Directory

InnoDB uses a fixed 16K page size — the natural volblocksize for the data directory. The InnoDB redo log (ib_logfile*) should sit on a dedicated Zvol with sync=always, so innodb_flush_log_at_trx_commit=1 actually delivers its ACID guarantees.

• Data directory: volblocksize 16K, lz4, sync=always
• Redo log: separate Zvol, volblocksize 32K, sync=always
• Undo/temp: optionally separate, sync setting per recovery requirement

sync=always: Why Databases Need It

sync=always is the single most important ZFS setting for reliable database operation. When a database confirms a COMMIT, the corresponding log sequence must be persistent on disk — no matter if the power fails a second later. Without sync=always, ZFS buffers the write in cache and reports the commit back before the data is stable. That is exactly what sync=always prevents.

The price: every synchronous write passes through the ZFS Intent Log (ZIL). Without a dedicated Slog device, the ZIL lands on the same pool that holds the data — which costs performance and is exactly where the Slog comes in.

More background is in our article on TrueNAS performance optimization and in our overview of ZFS compression and storage efficiency.

Slog Sizing: Small, Fast, Protected

A common misconception: the Slog has to be large. The opposite is true.

The Slog only absorbs synchronous writes between two transaction group commits — typically five seconds. For a database with 500 MB/s of synchronous log traffic, that is about 2.5 GB. A 16 GB Slog therefore covers nearly any SMB database.

Three properties matter more than size:

• Latency: A Slog must respond significantly faster than the main pool. NVMe SSDs with power loss protection (PLP) are mandatory.
• Endurance: A Slog writes continuously. Datacenter SSDs with high DWPD are the standard; consumer SSDs wear out fast.
• Redundancy: A Slog mirror is sensible for databases — losing a single Slog can in older ZFS versions mean losing the last transaction.

For the right hardware we advise individually — exact models and price ranges depend on the current market. A good starting point is our TrueNAS configurator, and for enterprise setups the R series with matching NVMe slots.

Snapshots and Replication for Transactional Databases

ZFS snapshots are atomic at the Zvol level — that is half the story. The other half is application consistency. A Zvol snapshot taken mid-transaction is “crash-consistent” (the database can recover), but not always “application-consistent”.

Best-practice patterns:

• MSSQL: VSS-based snapshots via the storage provider, or a short BACKUP DATABASE ... WITH COPY_ONLY right before the Zvol snapshot.
• PostgreSQL: pg_start_backup('label') / pg_stop_backup() (or pg_backup_start/pg_backup_stop from version 15) — the snapshot becomes a base for PITR recovery.
• MySQL/InnoDB: FLUSH TABLES WITH READ LOCK or better mysqldump --single-transaction for a logical backup alongside the snapshot. XtraBackup remains the alternative for hot backups.

Retention follows the classic pyramid: short-term snapshots every 15 minutes for 24 hours, daily for four weeks, monthly for one year. Details in our articles TrueNAS snapshots and replication explained and ZFS backup strategies.

On top, a ZFS replication to a second TrueNAS is recommended — ideally offsite or at least in a different fire compartment.

Restore Practice: From Snapshot Back Into the RDBMS

A backup that has never been restored is not a backup. For databases the restore path has more steps than for plain files.

A realistic workflow:

1. Clone the snapshot (zfs clone) instead of rolling back — keeps the original state safe if the clone-restore fails.
2. Present the clone as an iSCSI extent to a separate test server.
3. Start the database, check consistency (DBCC CHECKDB for MSSQL, pg_amcheck for PostgreSQL, CHECK TABLE for MySQL).
4. Application test with realistic read queries.
5. Only then does the clone become the production restore target, or data is transferred via pg_dump/mysqldump/T-SQL.

This exercise belongs on the calendar at least quarterly. In our disaster recovery concepts, the DB restore test is a fixed part.

Common Mistakes

• sync=standard instead of sync=always: Saves latency but risks the last transaction on power failure. Not an option for databases.
• A single Zvol for all DB files: Data and log on the same Zvol undo every optimization.
• Consumer SSDs as Slog: Without PLP, in the worst case you lose exactly the synchronous writes you wanted to protect.
• Snapshots without DB consistency: Crash-consistent is okay, application-consistent is better.
• No restore test: If you do not practice the restore, you have a gamble, not a backup.

Conclusion

TrueNAS is a solid, honest choice as database storage for MSSQL, PostgreSQL, and MySQL in SMB environments. Core building blocks: iSCSI with correctly chosen Zvols and recordsizes, sync=always for data and log, a right-sized Slog with PLP SSDs, clean snapshot processes with DB consistency, and a lived restore practice.

Get those right and you have a platform that reliably carries database workloads — without an enterprise SAN price tag. For a tailored sizing, get in touch or start with the TrueNAS configurator; prices we quote individually, since the hardware market keeps shifting.

FAQ

Is a single Slog device enough for databases?

Technically yes, in practice no. For production databases a Slog mirror of two PLP NVMe SSDs is recommended. 16 to 32 GB is almost always sufficient.

How big does a Slog need to be for a 500 GB database?

Database size does not matter — what matters is synchronous write throughput per five seconds. A 16 GB Slog covers typical SMB databases including log-area spikes in the hundreds of MB/s.

Can I run MSSQL databases over SMB?

MSSQL supports SMB 3.0 for database files, but we do not recommend it for production. iSCSI is more predictable and combines more cleanly with ZFS Zvols.

How often should database snapshots be taken?

A proven cadence: every 15 minutes for 24 hours, daily for 30 days, monthly for 12 months. At least the daily snapshots should be DB-consistent (VSS, pg_backup_start, XtraBackup).

TrueNAS SCALE or CORE for database storage?

Both use the same OpenZFS. For new setups we recommend SCALE (Linux base, active development). Background: TrueNAS SCALE vs CORE.

Can I replicate database Zvol snapshots to a second TrueNAS?

Yes, via ZFS replication — explicitly recommended for a second copy in a different fire compartment or site. Against ransomware, replica snapshots can be locked with readonly=on.

---

Planning a TrueNAS storage as the backend for MSSQL, PostgreSQL, or MySQL? Contact us — we size Zvols, Slog, and snapshot strategy to match your database and train your team in the restore practice.