First up in this series is deploying your two Windows Server 2016 VMs, which will be the SQL 2017 Always-on availability group nodes. Each VM should be configured with the same VM hardware properties. Selecting the right virtual hardware configuration is important, regardless of which hypervisor you are using. Memory, compute, networking and storage can all impact SQL 2017 performance. Remember that more is NOT always better, and in fact consuming more physical resources than you need (e.g. vCPUs) can actually hurt performance. Always take the time to 'right size' your VMs.
​Memory
​Memory configuration is very dependent on the SQL workload and your applications. In order for SQL server to use large page tables, which can boost performance, the VM needs at least 8GB of RAM. In general I don't reserve VM memory at the hypervisor level, but for tier-1 SQL VMs I would reserve the memory. This is an additional step in VMware environments, and is automatic with AHV as it doesn't overcommit VM memory as of the date of this post.
Database workloads love memory, as it is vastly faster to access data in memory than on disk. So you should not skimp on VM memory allocation, but neither should you over provision. Mentally reserve 2-4GB of VM memory for the OS, and then add on top of that how much memory you want to dedicate to SQL. Later in this series we will optimally configure SQL memory usage by adjusting SQL runtime parameters.
If your hypervisor supports hot-plug of memory, you can enable that feature without negatively impacting performance. However, remember that if you do increase the VM memory that the SQL memory runtime parameter we cover later in this series needs adjustment as well or the additional RAM will not be used by SQL. This requires a restart of the SQL instance.
Keep in mind that Microsoft SQL is NUMA (non-uniform memory access) aware, so there's not a major performance hit if you configure the VM for more memory than is in one NUMA node. But if you can keep VM memory allocation within a single NUMA node, that will maximize VM memory performance.
​vCPUs
​vCPU allocation is very dependent on the expected workload. If you allocate more vCPUs than exists on a NUMA node, performance may be negatively impacted. For example, if your server is dual socket and each socket has 8 physical cores, try and allocate 8 or fewer vCPUs to the VM.
Understanding NUMA can be a bit complicated, as different hypervisor versions and vendors handle it differently. NUMA configuration can also potentially impact SQL licensing, as the guest OS ​will see a differing number of sockets and CPUs depending on how the hypervisor presents the NUMA topology to the VM.
If you are using VMware vSphere, check out this blog post to get a good understanding of how they handle vNUMA in various vSphere versions. If you are using Nutanix AHV, check the latest version of the AHV Administration Guide on the support portal for current guidance and configuration steps. Tip: Search on the Nutanix portal for "vNUMA".
If you are using VMware vSphere, take note that enabling CPU hot add will disable vNUMA. So for all SQL VMs, I would NOT configure CPU-hot add. Schedule VM downtime to manually reconfigure the vCPUs and reboot the guest OS.
2018 has seen a number of CPU security issues arise, such as Spectre, Meltdown, and L1TF. Mitigations for these security issues can negatively impact CPU performance and even reduce the total number of usable logical CPU threads in a physical server. Make sure to take these mitigations into consideration when sizing your SQL VMs.
​Networking
Networking configuration is very straight forward. For Nutanix AHV there's only one virtual NIC type, so you can't go wrong. Just add a NIC to the VM, and you are set. If you are using VMware vSphere, use the VMXNET3 NIC. A single vNIC per VM is sufficient. Be sure to use the latest VMware tools or Nutanix AHV NIC drivers.
​Storage
​Properly configuring storage for SQL is a bit more complicated than networking. Configuration recommendations will differ for both the hypervisor you are using, as well as the underlying storage platform (e.g. SAN, Nutanix, etc.). If your storage vendor has recommendations for optimal SQL configuration disk configuration, follow them.
​​If you are using Nutanix AHV, use the "SCSI" disk type for all of the VM disks. If you are using VMware vSphere, I recommend using the default LSI SAS controller for the OS boot disk and the PVSCSI controller for all other disks. ​​​You can​ use the PVSCSI for the boot disk as well, but that disk should not see very many IOPS and requires additional manual steps to install Windows on a PVSCSI disk. ​​​See my blog post here for injecting the VMware drivers into your Windows image here.
As a starting point for disk configuration, the following is a good baseline for a basic SQL server using a generic storage back-end. You will very likely have a different configuration, but consider the disk layout below. Many times I will use mount points so you don't run out of drive letters, but for this example lets keep it simple. I also recommend unique drive sizes (even if just a 1GB difference) so you can easily match up hypervisor disks and in-guest disks. The drive letters below are what I'll be using through the rest of this series.
C: 40GB - OS Drive
D: 20GB - SQL Binaries
​G: SQL Data Files 1
​​H: TempDB data 1
I: TempDB data 2
​J: TempDB Logs
​K: SQL DB Logs
​L: SQL Backups
The Windows boot drive for SQL does not need to be huge. SQL binaries, databases and logs should all be stored on disks other than the C drive. So the boot disk should just be large enough for the OS, future patches/upgrades, swap space, etc. 40GB seems to be a fair number. Adjust as you see fit. All of the other disks ​are extremely environment specific and should be tailored to your requirements for both size and quantity.
​As mentioned above a production quality SQL server should have a number of guest disks ​for optimal performance. In vSphere environments all of the SQL-specific disks (e.g. data files, log files, temp DB, backups, etc.) should use the PVSCSI controller. However, for best performance you should add 3 PVSCSI controllers to the SQL VM and then evenly distribute the SQL disks over those three virtual controllers. If you are using Nutanix AHV no virtual controller card configuration is required as the SCSI disks are optimally configured under the covers. However, it is advised that you use the latest Nutanix AHV VirtIO drivers for best performance.
If you are using Nutanix based storage, regardless of hypervisor choice, the proper disk layout is needed for optimal performance. On Nutanix it is important to spread the SQL database files and TempDB files across multiple disks. For example, if you have a database that needs medium to high performance I/O it could be beneficial to spread the database files over 4 or more virtual disks. If the DB is 1TB in size, then configure it for 4x 256GB files on four separate disks as a starting point. The same goes for TempDB, using multiple TempDB files on several disks could increase performance. See the Nutanix SQL Best Practices Guide for more details.
​The C and D (SQL binaries) drives should use the default NTFS allocation size. However the remaining SQL disks should use 64K. Many moons ago partition alignment was an issue, but modern OSes are smarter and manual configuration is no longer needed. In virtual environments you have a choice of disk provisioning type (e.g. thin, thick, EZT, etc.). In nearly all cases thin provisioning is the preferred disk format, particularly on Nutanix storage. If your storage vendor has a different recommendation, follow it.
Finally, if you are using the Nutanix platform, follow the most current Nutanix SQL Best Practices guide for optimal container (datastore) configuration in terms of data reduction services such as compression, de-dupe, and EC-X. Recommendations can change over time as the Nutanix platform evolves.
Pro Tip: When creating the SQL VMs add the boot disk first, so that it has the lowest SCSI ID. This will enable Windows to put all of the needed partitions on your boot disk. Otherwise you may lose 500MB on another disk and create future headaches if you want to make disk changes down the road.
​Summary
​Optimizing the VM hardware configuration is very important for peak performance. VM configuration will vary slightly based on hypervisor and underlying storage platform. I strongly recommend creating a standard SQL VM configuration baseline, and using it as a template for all other SQL VMs. Then adjust CPU/Memory/Disks as needed for a particular instance. For VMware environments you can easily use VM templates for this purpose. Also remember to use the latest VM hardware version, if you have a choice.
Once you have installed Windows Server 2016 on two VMs, configured all of the drives, patched, and joined to the domain, then proceed to Part 3. Also make sure you have the SQL 2017 ISO handy and mounted to each VM. If you don't have another Windows VM in your environment that can host a simple and tiny file share (for the AAG file share witness) then deploy a third Windows Server 2016 VM with a single disk.
​SQL 2017 Installation Series Index
​​SQL 2017 Always-on AG Pt. 1: Introduction​
SQL 2017 Always-on AG Pt. 2: VM deployment
SQL 2017 Always-on AG Pt. 3: Service Accounts​
SQL 2017 Always-on AG Pt. 4: Node A SQL Install​
SQL 2017 Always-on AG Pt. 5: Node B SQL Install​
SQL 2017 Always-on AG Pt. 6: Cluster Configuration​
SQL 2017 Always-on AG Pt. 7: File Share Witness​
​SQL 2017 Always-on AG Pt. 8: ​AAG Setup
​​​​SQL 2017 Always-on AG Pt. 9: Kerberos (Coming)
​SQL 2017 Always-on AG Pt. 10: SSL Certificates (​Coming)
​SQL 2017 Always-on AG Pt. 11: Max Mem & Email Alerts (Coming)
SQL 2017 Always-on AG Pt. 12: Maintenance Jobs (Coming)
