Anyone using Ceph & MSSQL Server?

[crastinblack]

Hi all!

I'm looking at potential hypervisors for my company's network - we run a small Windows AD (2 DC's) with some Windows file shares and a SQL server with a couple of small databases on it. The hardware will be reaching end of life soon so I thought it's a good opportunity to explore the hyper-converged Ceph clustering available through Proxmox (something like the attached simplified diagram - granted the switches could be single points of failure so I'd put some redundancy in there too).

I guess for me, reliability and availability are more important than performance.

Does anyone else run a MS SQL server on a Proxmox Ceph cluster and if so, how significant is the decrease in performance compared to local storage? How have you arranged storage in Proxmox?

I'm guessing that a HA failover event where the SQL server migrates to another node in the cluster would leave the database in a crashed state so I'd be interested to hear from others if there is any way to mitigate this.

Many thanks

 

[Ruklaw]

If you are obtaining new hardware anyway then it's a good idea to spin up a test cluster when that hardware arrives to test if the performance is sufficient before committing to production.

I'm sure many people out there are running SQL servers on Ceph, although how well it works for you will depend on your performance expectations and needs, the hardware you obtain and the configuration of your ceph cluster.

Ceph works better the larger the cluster in use, so it can be a bit harder to realise the gains with small clusters - and it's inevitable that performance will be quite a way short of that possible in locally attached storage, especially in current era where hugely fast SSDs are widely available - in raw terms, the performance of ceph on small writes will be hugely below that of local storage, but it is likely still ample for your real world use case.

SQL server performance depends heavily on sync writes - that is to say, the speed at which data is both written and committed to the underlying storage medium - so it is important that you are running SSDs with power loss protection, or that your storage is behind a RAID controller with battery backup. The point of this is so that if the SQL server does crash or suddenly lose power, the database's data has been fully written to disk and so is recoverable, and having the correct hardware makes this orders of magnitude quicker (as data writes are acknowledged as written as soon as they are in the memory of the protected hardware).

I'm still in the planning phase of my Ceph/Proxmox cluster implementation, and one thing I will note is that if Ceph storage proves inadequate, Linstor/DRBD is much closer to local storage performance for the small writes that ceph struggles with, although does lack in ease of implementation and other features.

The way to mitigate the database being in a crashed state would be to migrate the database server off the node that is failing before it fails - this should be a fairly quick operation but obviously it isn't possible to know a node is about to fail usually! If you are performing maintenance obviously you want to evacuate running servers off that node before doing so to avoid potential disruption. The huge advantage of ceph is that if your node does fail, then you can bring your servers back up on your other nodes with absolutely zero data loss, instead of having to lose data by going to backups or replicas, or hoping that you can get the failed node back up and running.