MS-SQL as LXC on Ubuntu 20.04 vs Win10-VM in Ceph Cluster

[Sycoriorz]

Dear at all,

has someone of you experience regarding performance if MS SQL in LXC on Ubuntu or in Win10-Vm is better.

Background info:

Hardware:
Ceph Cluster 3 Node
NVME 9300 Max
RAM as needed

The Setup is for MS-ACCESS (ERP-System) connection over ODBC.
We have 80%write and 20%read
------------------------------------

I thought that the LXC has much more perfomance regarding the WIN10-VM.
Because i can use the NVME-Ceph-Pool directly without any virtualization.
Is this true?

Could there be any troubles regarding my ODBC connections?
Or is it as usual?

Someone knows if there is a bad side of providing MSSQL on Ubuntu.

many thanks

 

[oguz]

hi,

it's possible to make mssql work in ubuntu as far as i know, but i'm not sure how good is the support for linux systems in general.

if you prefer a VM you can still optimize the performance by changing settings. it's also possible to passthrough devices directly to VMs (though please note that this will inhibit live migration).

 

[ness1602]

MSSQL works okay in Ubuntu 20 LXC, i've tested 2019 version. There are some things that are not all that supported in Linux ,as oppose to Windows , but if you dont use XA or similar features you will be ok.

 

[WOJCIECH]

Best solution is running MSSQL Server in Linux in full VM, againts LXC container. WIN 10 is the worst option, because of updates, and problems with stability in long work period without shutdown. (I had inaccesible boot devices in monday morning, so....)

MSSQL in unprivileged container works ok, but problem is in situation when you want to snapshot running SQL Server. There are important differences. In VM you have command qm snapshot <VMID> --vmstate 1, after this you have snapshot with RAM, so you have more safety situation, because of SQL buffers. Your databases inside will be coherent, I'm not sure i 100 % how this works, but I suppose that qemu-agent (must install ) initiated inside VM process of flushing buffers - this is question for proxmox Staff - so in full VM, you have more features and elements safety for snapshot, which can works better in VM. In LXC containers command PCT snapshot has no --vmstate option, so you have only state of disks after snapshot. If you want to make coherrent state of LXC mssql server, you have to stop container before. Another problem is buffering od virtual disk. You can set writethrough on VM disk, or nocache for writting. in LXC container is more difficult.

 

[itNGO]

I would at first check it this is a supported Setup by Microsoft.
If you ever need Support with an issue in MSSQL else it is possible Microsoft says, not supported, no support for your issue....

 

[WOJCIECH]

I think it's supported, because instructions are on microsoft webpages:
https://docs.microsoft.com/pl-pl/sql/linux/sql-server-linux-overview?view=sql-server-ver16

 

[itNGO]

I think it's supported, because instructions are on microsoft webpages:
https://docs.microsoft.com/pl-pl/sql/linux/sql-server-linux-overview?view=sql-server-ver16

Can't find any word about LXC/Container....

 

[WOJCIECH]

Can't find any word about LXC/Container....

Ok. Sorry. I understood, that problem is in no support after installation and working MSSQL under the Linux atall. Right. Microsoft documentation says only about operating system. Nothing about running in container or VM. Important thing is, that ZFS file system is not supported for MSSQL in Linux, so LXC under Proxmox with ZFS file system is not definitely supported, ...but it works. (I found instructions of Solaris for using ZFS file system for Databases Servers, and MSSQL cache parameters, VOL size, etc.). Conclusion is the same: Safer option for production is using full VM of MSSQL under Linux, because yo can use inside VM xfs system file, so It will be compatible with Microsoft instruction.