Your Guide to Virtualizing SQL on vSphere (Part 1)

By now, a lot of tier-2, tier-3 SQL Servers has been virtualized. Customers are seeing more availability, better agility with those SQL Servers running in a virtualization environment. That’s leading customers to consider moving tier-1 mission critical SQL Servers onto the virtualization environment. A properly configured ESX is crucial to the successful deployment of a tier-1 SQL Server. In this post, I would like to talk about some storage configuration guidelines that would help maximize SQL Server performance on VMware.

Virtual machine disk files can be deployed in three different formats: thin, zeroedthick, and eagerzeroedthick. Thin provisioned disk enables 100% storage on-demand, disk space is allocated and zeroed out at the time disk is written. Zeroedthick disk is pre-allocated, but blocks are zeroed out by the hypervisor during the first time the disk is written. Eagerzeroedthick disk is pre-allocated and zeroed initialized during provision time. There is no additional cost for zeroing out the disk during run time.

For optimal disk access performance, use eagerzeroedthick disks for SQL Server data, transaction log, and tempdb files.

Partition alignment is a well discussed topic for SQL Server. An unaligned partition results in a track crossing and additional IOs, incurring penalty on latency and throughput. See figures below.

On VMFS, partition alignment should be configured at the VMFS level and Windows level. Beginning with ESX v3.x, VMware automatically aligns new partitions to a 64KB boundary when you create a VMFS from vCenter. In ESX v5.0, new partition is automatically align to the 1MB boundary. You may also manually align your VMFS partition. See http://www.vmware.com/pdf/esx3_partition_align.pdf for instructions on manual alignment.

For Windows 2008 and above, partition alignment is usually performed by default. The default for disks larger than 4 GB is 1 MB; the setting is configurable through Windows registry setting HKLMSYSTEMCurrentControlSetServicesVDSAlignment. However the default partition alignment may be altered if system is created with OEM setups. It’s a good practice to always check to confirm partition alignment. For more information, see Disk Partition Alignment Best Practices for SQL Serverhttp://msdn.microsoft.com/en-us/library/dd758814.aspx.

SQL Server files have very different disk access patterns. Placing SQL Server binary, data, transaction log, and tempdb files on separate storage devices provides maximum flexibility, and improves performance. Consider the following best practices for deploying a tier 1 mission critical SQL Server.

• Place SQL Server binary, log, and data files into separate VMDKs. In additional to the performance advantage, separating SQL Server binary from data and log also provides better flexibility for backup. The OS/SQL Server Binary VMDK can be backed up with snapshot-based backups, such as VMware Data Recovery. The SQL Server data and log files can be backed up through traditional database backup solutions.
• Maintain 1:1 mapping between VMDKs and LUNs. When this is not possible, group VMDKs/SQL Server files with similar I/O characteristics on common LUNs.
• Use multiple vSCSI adapters. Place SQL Server binary, data, log onto separate vSCSI adapter optimizes I/O by distributing load across multiple target devices.
• Test prior to deploying SQL Server. Storage sub-system should be able to achieve IO requirements with an acceptable latency in additional to capacity requirements. IOMeter can be used to simulate SQL Server IO patterns. Below table are example SQL IO patterns that can be used to test storage sub-system performance without invoking an actual SQL Server workload.

-Wanda

Wanda He, Technical Solutions Architect

This blog is part of a series on Virtualizing Your Business Critical Applications with VMware. To learn more, including how VMware customers have successfully virtualized SAP, Oracle, Exchange, SQL and more, visit www.vmware.com/go/virtualizeyourapps.