VMware Cloud Community
tdubb123
Expert
Expert

SQL Server VM and SCSI controllers

building SQL server VM and need some advice on drive/controller placement

going to use PVSCSI cpontrollers

 

SCSI0: OS, BAckup Drive

SCSI1: SQL Binary/software, SQL DAtabase

SCSI2: SQL Logs

SCSI3: TempDB

 

any suggestions?

 

Reply
0 Kudos
5 Replies
continuum
Immortal
Immortal

Assumeing you will use Win 2000 VMs you need Buslogic scsi-controllers ... in other words - what OS will you use ???

Ulli


________________________________________________
Do you need support with a VMFS recovery problem ? - send a message via skype "sanbarrow"
I do not support Workstation 16 at this time ...

Reply
0 Kudos
tdubb123
Expert
Expert

2016 server. 

Reply
0 Kudos
tdubb123
Expert
Expert

maybe 2019

Reply
0 Kudos
a_p_
Leadership
Leadership

I'd likely follow VMware's recommendation, and use the preconfigured controller for the VM.

What you should do, is to follow Microsoft's recommendation regarding the NTFS Cluster size when you format the drives.

André

Reply
0 Kudos
PatrickDLong
Enthusiast
Enthusiast

There are myriad considerations to properly deploy and optimize a SQL vm; it really depends if you are tuning this vm for maximum performance or more for fairness and being a good neighbor in a shared environment.  I like your disk distribution across controllers as you have it written, but would consider moving the SQL binaries to the OS disk.in order to isolate SQL data to a unique controller. General recommendations:  Use only PVSCSI controllers and format the OS/application partition with standard 4k clusters and any SQL data/log/tempDB partition with 64k cluster size and UseLargeFRS option.  Always install the latest vmTools on the vm.  Try to keep the vm within a single NUMA node boundary for physical cores (this depends on your host processor mfr/generation) and also limit vRAM to memory owned by that single NUMA node.  You didn't give any details of your desired SQL vm specs, ESXi version, nor the underlying host hardware or back-end storage type (traditional datastore or vVols?) and connectivity, so it's tough to give general advice beyond this.  If your vm needs to be larger than the resources available in a single NUMA node, you are talking about a wide vm which requires care to properly configure. If you need the resources of >2 NUMA nodes then you are talking about Monster vm's which require significant design and configuration effort to properly tune. 

In a basic shared environment with limited host resources serving many vm's I would resist the urge to do too much additional vm tuning beyond the few things listed above - vSphere default settings are generally designed for fairness and you run the risk of becoming a noisy neighbor by changing defaults if you are unsure of the impact on the rest of your environment.

If you are tuning for maximum performance and can isolate your SQL vm on a host or cluster environment with no CPU over-commitment that would be ideal - then you can start to also look at things like specifying cores/socket to match the underlying CPU architecture, modifying PVSCSI driver queue depths in the guest OS, modifying queue depths on your host hba's, etc. to further increase I/O performance, but all of these require a deep understanding of the physical environment backing this vm.  The VMware Extreme Performance series gives a ton of great information in this regard, as does the ARCHITECTING MICROSOFT SQL SERVER ON VMWARE VSPHERE Best Practices Guide.  Also check out old VMWorld presentation videos on the topic:  https://youtu.be/5EJu2ER-aLI

 

Reply
0 Kudos