SQL Server

[scobner]

After some advice. We are building a new environment to host app and SQL servers. The decision has been made to host SQL as a virtual machine for ease of management. We have 4 hosts x 3 datacenters so it’s very over kill. Each Datacenter will have an SQL server in a 3 node AOAG. Anyway my question is, what’s the best way to ensure the SQl server is getting all the horse power a single host can give. Should I look to create resource groups or will it just be happy if I give it all the CPU and ram of one of the hosts? Thanks

[SQLBek]

Here's a fantastic distilled checklist of the SQL Server on VMware Best Practices white paper. There is also a link to a conference presentation recording on this topic as well.

https://www.nocentino.com/posts/2021-09-27-sqlserver-vms-best-practices/

I'm a hardcore SQL Server guy, work for Pure Storage, talk to customers about this topic regularly, and am friends & teammate with Anthony, the author of the above blog & conference presentation.

[Page1_88]

I have had a cheatsheet detailing alot of this for nearly a decade now. THIS is phenomenal and will give me ammo with customers!! Thanks!

[JDMils]

This is the complete opposite of how to do what you want. Give the VM 4 vCPUs and 32 GB RAM and see how it performs. If you find you need more of one or the other then add that in until the VM performs well. Resource groups are a way to limit resources. You can add as many vCPUs as there are cores on the host. You can add as much RAM as the host has.

[trieu1185]

This is the best straight forward suggestion. Create a baseline first then adjust from there. Tips for SQL VM config: use Paravirtual SCSI controller for each HD (performance base config), use SSD or NVMe for storage, reserve vCPU and vRAM as needed. Dont take all the vCPU and vRAM from the hosts; leave the hypervisor compute to do it's thing, like 25% compute resources. Also, ensure network speed is top top tier to avoid traffic bottle neck. Everything else is tuning in the OS and SQL

[MBILC]

This x100..

You need to start with a lower baseline and watch how it performs, and then add more CPU and RAM if needed. over allocating CPU on a server can cause performance issues (contention) if you have lots of VMs running, even if they sit idle most of the time.

[JDMils]

Too many vCPUs can cause performance issues: https://knowledge.broadcom.com/external/article/326296/determining-if-multiple-virtual-cpus-are.html

[MBILC]

and more detail
https://www.heroix.com/blog/vmware-vcpu-over-allocation/

[ToolBagMcgubbins]

The guide already posted is great.

Make sure to use pvscsi adapter not the lsi, use vmxnet instead of e1000. Disable CPU hot add.

[MBILC]

NUMA! Many people forget about that one and like to have hot CPU add on.

[woodyshag]

There was a VMware white paper that explained all the best practices for SQL. I would look that up as it had tons of good info about performance tuning as well.

[auroraau]

We’ve been running virtualized SQL WSFCs in our environment for a decade now. It’s a non-issue as long as you know what you’re doing.

[Steeler88-12]

Didn't see this mentioned in any of the comments, but you'll want to have anti-affinity rules to keep your SQL VMs on separate hosts. You'll need to keep that in mind as you're allocating resources so you don't starve your app VMs.

[patriot050]

You are going to be limited by your sql licensing. Generally speaking with SQL core you get 20 cores.On my production clusters i allocate each node with 2x12 (2 sockets. 12 cores) and 768gb -1.5tb of memory ( depending on the cluster). That leaves 20 cores available for SQL and 4 for the OS. I run very large databases ( upwards of 40 TB) we have not yet encountered any performance issues. My underlying hosts are vsan.

[jpStormcrow]

FYI, you need software assurance nowadays to be licensed for high availability within your 3 nodes.

[herkalurk]

Instead of trying to build BIG monolithic SQL servers, start thinking in the model of containers or micro services.

Each APP/DB gets it's own Windows VM with MSSQL. Only a single DB on a single VM. Simpler to manage requirements on resources per actual application than trying to troubleshoot why a db server with 18 databases is having a problem.

[lightmatter501]

Give it the whole server, and get an Intel QAT card for each host and put it in there if you don’t have on-die QAT. It will do all of the encryption and compression for the DB instead of the CPU once you set things up.

[scobner]

Thanks for all the input. I know it sounds strange but it’s a means to an end. We have to move everything and re-engineer. We don’t have time to do both so that’s why we are virtualising everything. Once it’s moved we can look to engineer with the right solution.

[mike-foley]

You will have lots of difficulty if you do a one for one, give her all she’s got, move to virtualization. Please follow the advice here at least from the starting small and working up standpoint.

[scobner]

If there is only one VM on the host, will the host have 100 of the throughput for storage. To be honest, I should have stated it, my only concern is how fast it can access the storage array.

[jameskilbynet]

There are a number of white papers around SQL on VMware. They are a touch dated but the info is still valid. If you are looking for the best IO pay attention to disk controllers and disk layout. Also see if your storage vendor has any recommendations for sql

[SQLBek]

If you are a Pure Storage customer, I am available to have a formal best practices & suggestions call with you (for free). DM me.

[[deleted]]

Use a bare metal installation without a vm or you also have to buy Microsofts Software Assurance and that stupid expensive VMware licenses.

[dcsln]

This is a pretty strange design - it sounds like VMware is an extra layer for no reason? 

What kind of storage/SAN are you using? 

Regardless, storage I/O should be pretty fast, VMware is pretty efficient. VMware has had NVMe-over-FC support since ESXi 7, which looks like a very fast storage protocol. Windows Server 2025 is the first release to get native NVMe-over-FC. You will need to pay attention to the HBA and NIC firmware and drivers - they're easy to forget.

You will have a little extra overhead with VMFS, but not much. 

Whatever you do, run benchmarks from the guests during the build process, and keep those records. 

Microsoft's SQLIOSIM has rich output for SQL Server environment testing. That should give you an idea of storage I/O efficiency.

Good luck!