Best Practices and Considerations for Virtualizing Microsoft SQL Server on VMware

16 05 2010

Yeah yeah I know…this topic has been blogged about a million times, so why do it again?  Well, I continue to have people ask me about virtualizing SQL; can it be done, should it be done, how do you go about doing it, and most important, what are the best practices?  While there are a ton of resources on VMware’s website, on a bazillion different blogs, and on Microsoft’s site, I thought it would be a worthwhile exercise to consolidate the main points as well as a good reference list of resources if you wish to dig deeper…enjoy!

Storage Considerations

The main, cardinal rule with virtualizing SQL, or any I/O resource intensive workload for that matter, is to treat it exactly the same as if it were physical from a disk perspective.  This is where most people get into trouble and where 99% of performance issues originate, as they architect VMFS datastores for size and not performance.  Basically, if you have a RAID 5 4+1 disk group hosting your DB in a physical environment, then it should be exactly the same if the server is virtualized.  If you are unsure exactly what the I/O requirements are for your particular system(s) than a perfmon collect should be executed against said server(s) to solidify the exact numbers.  The Perfmon capture should focus on Disk Transfers/sec, Average Disk Queue Length, Average Disk Write Queue Length, Average Disk Read Queue Length, and % Disk Time.

Ensure that database files are not placed on the same LUN’s as log files.  Due to the random nature of database access and the sequential nature of log file updates, mixing these 2 disparate types of data can serve to only increase random storage behavior and therefore significantly impact performance.

Throughput is another commonly overlooked area when architecting a high performance virtual environment.  If you’re using Fibre Channel for storage connectivity, and specifically 4GB, than it’s not as much of an issue.  However, if you are using NFS or iSCSI across 1Gb Ethernet than it’s quite possible to hit throughput limitations.  Usually the rule of thumb for iSCSI is if your 95th percentile on throughput is around 60 MB/s or greater than it’s time to either multi-path, which can be challenging with iSCSI, or step up to 10Gb Ethernet, or use FC which is usually the less costly option.  If you are already using FC and are encountering throughput limitations than consider using a true multipathing solution such as PowerPath/VE from EMC.  Remember that while one VM may not constitute this degree of performance, you more than likely will be running more than 1 VM instance on a single host and therefore you will be significantly consolidating I/O requirements.

This topic has been beat to death on many blogs so I’m not going into a ton of detail, but be sure to follow MS recommended best practices based upon your OS version and partition size to correctly align disk partitions for better performance of your storage.  In general VMFS partitions should be aligned on 64K boundaries, which is automatically configured when creating datastores from within vCenter.

Memory Considerations

If you have a SQL server that requires more than 4GB or RAM then you should be using an x64 based operating system.  Memory over-commit at the host level or memory provided through tuning to circumvent 32-bit limitations can cause adverse performance issues.  If more than 3GB of memory is needed for SQL consider using a 64-bit OS and corresponding version of SQL.  Be sure to set memory reservations for SQL VM’s at the host level especially if memory is being over-committed.  Set ‘Min/Max memory’ within SQL per MS best practices.  Last, if ‘Lock Pages in Memory’ is set be sure to again reserve memory for the VM mirroring the same amount recognized within the OS.

Network Considerations

Just as with storage, when systems are virtualized and consolidated to a subset of physical hosts, the I/O requirements are not reduced as a result.  At a minimum separate network interfaces should be utilized to avoid traffic contention.  This includes separate physical interfaces for the Console, VMKernel (vMotion, iSCSI, NFS), and VM traffic.  In addition multiple physical interfaces should be used for storage and VM network traffic to provide adequate throughput.

To ensure the best network performance for VM’s, the vmxnet driver should be used for VM network interfaces.  The vmxnet driver is a paravirtualized networking driver that works in conjunction with the hypervisor to reduce virtualization and CPU overhead.  In vSphere, or ESX 4.x, the PVSCSI driver should be used for virtual storage adapters for VM’s.  The paravirtualized SCSI driver, similar to the vmxnet driver, enables better throughput for storage I/O intensive VM’s while also ensuring lower CPU utilization at the host level.

http://www.vmware.com/resources/techresources/10065 (Review of performance with VMXNET3 driver in vSphere)

http://www.vmware.com/pdf/vsp_4_pvscsi_perf.pdf

If it is uncertain whether or not a particular workload can benefit from the PVSCSI driver, than review the following VMware KB.

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1017652

When supported, Jumbo Frames should also be enabled across the network infrastructure for better network throughput, especially when iSCSI is being used, while also reducing the total number of packets transmitted as well as reducing CPU utilization on both the transmit and receive sides.

http://www.vmware.com/files/pdf/vsphere_performance_wp.pdf

Performance Benchmarking

Everyone including myself has been guilty of incorrectly interpreting a performance benchmark.  If a system is critical enough then it should warrant executing a performance test in your environment on the exact system where the deployment is planned to reside.  There are several free and inexpensive tools available, such as Dell’s DVD Store, TPC-E, Benchmark Factory, etc, which can facilitate a load test which will valid your specific configuration.

General SQL Server Best Practices

General SQL design best practices should be followed regardless of whether or not the server is physical or virtual.  This includes running a single instance of SQL on a individual server for very performance intensive workloads.  While this comes at a higher price it can improve performance and ensure issues such as runaway transactions will not impact several different databases in your environment.

Additional Resources

Availability Guide for Microsoft SQL® on VMware vSphere 4
A 2009 paper describing different options available for achieving High Availability for SQL Server on vSphere. Describes different alternatives including VMware HA, Log Shipping, Database Mirroring, and Failover Clustering.

Performance and Scalability of Microsoft SQL Server® on VMware vSphere 4
A 2009 paper describing detailed performance analysis of Microsoft SQL Server 2008 on vSphere for an enterprise database workload.

Best Practices for Virtualizing SQL Server
A 2009 paper describing best practices for SQL 2005 and 2008 on virtual machines.

Performance Study: SQL Server Workload Consolidation
A 2008 analysis of the impact of server consolidation on SQL Server performance.

White Paper: SQL Server Performance in VMware Infrastructure
A 2007 performance analysis of SQL Server 2005 on ESX 3.0 by VMware and HP.

White Paper: Benchmarking Microsoft SQL Server Using VMware ESX Server 3.5
A 2008 benchmark of SQL OLTP on ESX 3.5 by Brocade.

Solution Brief: Microsoft SQL Server, VMware Virtual Infrastructure, and EMC Celerra NS Storage Server
A 2008 Implementation guideline to virtualizing SQL Server with EMC Celerra Storage.

Reference Architecture Guide: Microsoft SQL Server, VMware Virtual Infrastructure, and EMC Celerra NS Storage Server
A 2008 architectural overview for virtualizing SQL Server with EMC Celerra Storage.

“Adding a Virtual Disk on an Auxiliary Virtual SCSI Controller to a Virtual Machine Moves Other Devices to New PCI Slots” http://kb.vmware.com/kb/1513

“Benchmarking Microsoft SQL Server Using VMware ESX Server 3.5” http://www.vmware.com/files/pdf/benchmarking_micrsoft_sql_vmware_esx_server_wp.pdf

“Comparison of Storage Protocol Performance” http://www.vmware.com/files/pdf/storage_protocol_perf.pdf

“DRS Performance and Best Practices” http://www.vmware.com/files/pdf/drs_performance_best_practices_wp.pdf

“Large Page Performance” http://www.vmware.com/files/pdf/large_pg_performance.pdf

“Microsoft SQL Server 2005 Virtualization in the Dell Scalable Enterprise” http://www.dell.com/downloads/global/power/ps4q06-20060405-Muirhead.pdf

“Networking Performance: VMware ESX Server 3.5” http://www.vmware.com/files/pdf/ESX_networking_performance.pdf

”Performance Best Practices and Benchmarking Guidelines” http://www.vmware.com/pdf/VI3.5_Performance.pdf

“Performance Characterization of VMFS and RDM Using a SAN” http://www.vmware.com/files/pdf/performance_char_vmfs_rdm.pdf

“Recommendations for Aligning VMFS Partitions” http://www.vmware.com/vmtn/resources/608

“Scalable Storage Performance” http://www.vmware.com/resources/techresources/1059

“Setup for Microsoft Cluster Service” http://www.vmware.com/pdf/vi3_35/esx_3/r35u2/vi3_35_25_u2_mscs.pdf

“SQL Server Best Practices” http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

“SQL Server Workload Consolidation” http://www.vmware.com/pdf/SQL_Server_consolidation.pdf

“SQL Server Performance in a VMware Infrastructure 3 Environment” http://www.vmware.com/files/pdf/SQLServerWorkloads.pdf

“Storage Top 10 Best Practices” http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

“Virtualizing Microsoft SQL Server” http://www.vmware.com/solutions/business-critical-apps/sql/

Advertisements

Actions

Information

7 responses

13 08 2010
Henry Awad

Great article and extremely well researched. The additional resources section and links are greatly appreciated. The M3 pictures on the top of your blog page is priceless. I’m a huge fan of BMW performance cars and have an appreciatiation of like minded people. I am now a follower of your blog and look forward to future posts.

16 08 2010
matthensley

Hi Henry,

I certainly appreciate the kind words, and a fellow BMW junky is always a welcome friend in my book.

Thanks,

Matt

25 08 2010
Henk Arts

Matt,
Big kudo’s for this article and research!

I have put a link to it on my blog 😉 here:

http://www.vmstarter.nl/2010/08/best-practice-ms-sql-or-ms-exchange-running-virtual/

keep up the good work!

tHENKs

1 10 2010
Kartik

Hi- Nice article. Could you tell me if it is possible to combine both SQL Mirroring and Failover clustering on VMWare Vspsher4 setup?

10 10 2010
matthensley

Hi Kartik,

Thanks for the kind words and for reading my blog. I’m a bit confused by your question…could you elaborate further? Are you asking whether or not you can use SQL Mirroring on top of an ESX cluster? If that is the case then the answer is yes. You can also use Microsoft Clustering Services in conjunction with SQL on top of an ESX cluster but there are some specific requirements such as physical mode RDM’s in that scenario.

6 03 2011
Microsoft SQL Server on VMware – Best Practices Guide « Techpot’s Blog

[…] Best Practices and Considerations for Virtualizing Microsoft SQL Server on VMware is available here. […]

4 08 2011
Microsoft SQL Server on VMware – Best Practices Guide | All Things Virtual...

[…] Best Practices and Considerations for Virtualizing Microsoft SQL Server on VMware is available here. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: