Ceph - feasible for Clustered MSSQL?

[arankaspar1]

Looking to host a clustered MSSQL DB for an enterprise environment using ceph to remove a NAS/SAN as a single point of failure.
Curious about performance, requirements are likely not very much and no multi-writer outside the cluster itself.
However... as I understand writes can be very bad with ceph.

Question is...
If you had the perfect storage hardware for a 3 node ceph cluster to bump up writes... what would it look like?
Bonus round: would it be ideal for a small clustered MSSQL DB?

From what I read ceph typically suffers from reduced write-performance, due to the nature of having to commit writes to all 3 nodes?
The cache options which I've read on the tips & tricks page state that in most cases caching either slows things down or increase susceptibility to data-loss.
For these options that are slowing things down... could you potentially "add-money" to get better hardware with the IOPS/performance spec making it worth-while?

Given today's drive options and a big budget... how would you throw money at the problem? Ideally focused on reducing ceph drawbacks of lower write latency/bandwidth without compromising the stability and data integrity during a power outage event.

I have a few Dell precision 7810's with very capable Xeon options (for ideally more striping of data over many drives) w/ boat loads of pcie lane bandwidth (nvme).
I have a few IT-mode flashed H310 - w SAS break-out cables (12Gb).

I'm thinking eight SAS 12Gb Enterprise Endurance SSDs for Data on Ceph....and if possible can RocksDB live here as well? Or here...
Four small NVMe's in local mirrors (R10) for the PVE install which I'm presuming is where host page cache lives for "writeback"?

Also from what I've read Power Loss Protection drives are really the way to go (a bit harder to find imo unfortunately).

I'm thinking...
Would the above described suffice for this very small clustered MSSQL DB or...
If this config doesn't make sense then it may be better to have the SQL on a local mirrors pool (non-ceph) storage, with periodic replication to ceph FS?
Not even sure what how the SQL clustering part looks but I image it requires Failover cluster manager which would be installed on the windows VMs.

Any help is appreciated. I come from a FreeNAS/ESX background and I'm very new to proxmox so go easy on me lol.
Obviously the enterprise version of proxmox would be the way to go.

 

[Nemesiz]

You have to ask what Ceph will solve for you. Scalable, failure ....

If you decide to use Ceph in my short experience and other people suggestions:

1. Get fast network as possible. Network latency have big role.
2. Get SSD/NVME enterprise grade to survive the load.
3. More CPU cores not always wins VS CPU frequency. Ceph services are single thread mostly. Perhaps OSD likes cores.
4. Don`t forget Ceph ram usage.
5. Disable write cache in drives https://www.youtube.com/watch?v=bl6H888k51w&t=980s

Would I go into Ceph cluster setup ? Yes. I moved from ZFS to Ceph and I`m happy how can I maintenance servers and do other stuffs.
I`m not running MSSQL, just MariaDB and other stuff. Its runs for my needs. If I had to use local ZFS for database I could get some better results at some points, compare to only 3 nodes cluster, but ZFS have limited failure protection. Of course you will have to maintenance Ceph if you don't want to lose data.

 

[gurubert]

If you want to run MSSQL in a clustered setup use local storage for its nodes.

There is no need to replicate on the storage level if the DB replicates its data on the application level.

 

[UdoB]

If you had the perfect storage hardware for a 3 node ceph cluster

I would never start a serious business with three nodes.

Ceph requires some more resources to be reliable and fast; I've written down some things I've noticed: https://forum.proxmox.com/threads/fabu-can-i-use-ceph-in-a-_very_-small-cluster.159671/

Disclaimer: I've dropped my six node Ceph with 12 ODSs and went back to ZFS only - after 15 months of productive use in a Homelab with slow (2.5 GBit/s) network.

 

[arankaspar1]

I have also 3 Emulex 10Gb converged network adapters to test with... again this is a small test environemnt.
Each CNA has 10Gb LC fiber SFPs in them. Thought was to do point-to-point ring with FRR.
10Gb I know is probably the lowest you can go usably. I'd like to avoid switche(s) if possible. (another failure point).

I'll have to get a measure of the IOPS usage from a running production system (Hyper-V) to see how much the disk actually gets hit.
Maybe I'll try this https://iometer.org/doc/downloads.html but if you know of any windows storage benchmarks that's helpful.
So yeah... prod is running all Hyper-V and MPIO or MCS over DAC to a Synology.
I just would love to...
1. Move to prox to patch both host/vm level anymore. 2. Remove any single point of failure (synology).

 

[alexskysilk]

As @gurubert mentioned, your solution doesnt match your problem description.

you dont need 3 servers for a mssql cluster. you need two. You dont even need a nas/san at all, you have multiple options included windows failover cluster and storage spaces direct. Microsoft has multiple documents on the various methods to do this- I'd start there.

 

[arankaspar1]

That's advice. I'll investigate those routes instead.
What do you recommend for local storage? LVM or ZFS?
I've heard LVM out-performs it but obviously doesn't have a RAID system (that I'm aware of).

Gen 4 drives should suffice?
I'm able to find some Micron/Seagate models with PLP.
https://www.reddit.com/r/HomeServer...ommendation_for_a_server_grade_nvme_ssd_gen4/

I'm assuming it would be best to get two PLP drives in a ZFS mirror?