Best practice for MSSQL on linux?

killmasta93

Renowned Member
Aug 13, 2017
956
56
68
30
Hi,
I was wondering if someone could share some success stories or recommendation on what type of configuration i should put on proxmox? Currently i put disk cache write back, i saw that the SQL uses more reads then writes, i tried the write though but it was not as good as the write back. Not sure what else could influence? Currently running MSSQL on linux
Thank you
 
Depends on a lot of things:
- backend storage type
- settings of backend storage (e.g. blocksize)
- frontend storage type
- settings fronend storage (e.g. blocksize)
- number of virtual CPUs
- NUMA nodes
- overall memory settings
- linux distribution for MSSQL
- ...
 
Thanks for the reply, currently have a dell 620 with 24 cores and 48 gigs of ram, so how would you recommend?
 
Thanks for the reply, currently have a dell 620 with 24 cores and 48 gigs of ram, so how would you recommend?

I should have written my text as question. Just imagine my bullet points are questions and please answer them. Your physical machine is capable (please post also your hardware storage setup), but the settings in software are more important now.
 
Thanks for the reply, sorry will answer them
Code:
 backend storage type
ZFS

Code:
settings of backend storage (e.g. blocksize)
i installed the vms on the rpool

Code:
- frontend storage type
- settings fronend storage (e.g. blocksize)

not sure what you mean here?
Code:
number of virtual CPUs
12 cpu 2 sockets and 6 cores with numa=1

Code:
 overall memory settings
32 gigs of ram


Code:
linux distribution for MSSQL
ubuntu 16.04

Forgot to mention on the current machine its a dell r620 is on a raid 10 zfs 7200rpm
but were going to to put proxmox on a proliant dl380 gen9
with
Code:
600GBsas disk 15k rpm
600GBsas disk 15k rpm
600GBsas disk 15k rpm
600GBsas disk 15k rpm
600GBsas disk 15k rpm
2tb sas 512E disk  7200rpm
2tb sas 512E disk  7200rpm
2tb sas 512E disk  7200rpm
2tb sas 512E disk  7200rpm
1.8tb SAS 512E DISK 10k rpm
1.8tb SAS 512E DISK 10k rpm
1.8tb SAS 512E DISK 10k rpm
1.8tb SAS 512E DISK 10k rpm
1.8tb SAS 512E DISK 10k rpm

maybe might be an advantage on the 15krpm the mssql?
 
maybe might be an advantage on the 15krpm the mssql?

Sure. The faster, the better.

Code:
- frontend storage type
- settings fronend storage (e.g. blocksize)
not sure what you mean here?

The settings inside of your VM. Do you use VirtIO, SCSI with VirtIO, SATA, IDE. Also, what filesystem do you use inside of your guest. I suppose you're running a "real" VM and not a container?
 
Thanks for the reply, correct i use VirtlO and the filesystem inside the VM is ext4 with writeback cache
 
Thanks for the reply, so howcome on the disk it also shows VirtlO so always choose SCSI?
 
Thanks for the reply, duly noted, but out of curiosty how come it also shows on the disk to use VirtiO?
 

About

The Proxmox community has been around for many years and offers help and support for Proxmox VE, Proxmox Backup Server, and Proxmox Mail Gateway.
We think our community is one of the best thanks to people like you!

Get your subscription!

The Proxmox team works very hard to make sure you are running the best software and getting stable updates and security enhancements, as well as quick enterprise support. Tens of thousands of happy customers have a Proxmox subscription. Get yours easily in our online shop.

Buy now!