INTRODUCTION

As computing power, memory and disk space are readily available on virtualization hosts, the Disk I/O is often over-looked. As a matter of fact, it’s the number one factor affecting performance of virtualized Microsoft SQL Server. The problem is two-fold. First, the abstraction layer between virtualization host and virtual machine slows down the Disk I/O. Second, multiple virtual machines running on the same host can end up competing for I/O resources.

In general, virtualized SQL Servers are better suited for non-mission-critical or less demanding applications. Production SQL Servers running mission-critical and I/O intensive applications like Microsoft Dynamics GP can be virtualized, but special consideration needs to be given to Disk I/O configuration.

SEPARATION OF DATABASE FILES

The cardinal rule for virtualizing SQL Server with I/O intensive workload is to treat it exactly the same as if it were running on a physical server. The Data and Log files for SQL Server databases should be located on different Virtual Disks (VHDs) or SAN Logical Units (LUNs). Microsoft Dynamics GP uses TempDB quite heavily, so it’s recommended to move TempDB to a separate VHD or LUN as well. The Operating System and Program Files should also reside on VHDs or LUNs separate from SQL Server databases.

PHYSICAL DRIVES AND RAID CONFIGURATION

RAID 5 or 6 should be used with caution on virtualization hosts, as they have slower writes. If there are performance issues, use RAID 10 (RAID 1+0) instead. The advantage is faster I/O at cost of reduced disk space. The physical disk drives for standard or RAID configuration on the virtualization host should be at least 7200 RPM with 3.0 GB/sec interface transfer rate. Using Solid State Drives can also drastically improve performance but they are 8 to 10 times more expensive, which makes them impractical for systems with larger disk storage requirements.

RECOMMENDATIONS FOR STORAGE AREA NETWORKS (SANs)

When building Virtualization Servers with a Storage Area Network (SAN) try to use a Fiber-optics Channel (FC) for storage connectivity instead of iSCSI as the throughput on iSCSI interface will be limited by the network card. iSCSI can be used with 10 GB Ethernet NIC, which makes it more comparable to FC.

Figure 1: Microsoft Dynamics GP using Virtualized Microsoft SQL Server & SAN

MINIMIZE VIRTUALIZATION OVERHEADS

Fixed virtual disks are recommended over dynamic virtual disks for virtual machines, allowing disks to dynamically expand results in loss of I/O performance. Pass-through disks can also be used to gain faster and direct access to the physical drives or SAN logical units (LUNs).  Microsoft Windows Server 2012 with Hyper-V now includes a Virtual Fiber Channel Host Bus Adapter that can provide direct access to SAN logical units (LUNs).

USE VENDOR RECOMMENDED VIRTUAL CONTROLLERS AND DEVICE DRIVERS

Make sure the best controller type is used for higher performance. Virtualization solutions offer several virtual controller types for better guest OS compatibility with some being less efficient than others. If your virtualization solution (Microsoft Hyper-V, VMWare, Citrix XenServer, etc.) offers specific drivers for the virtual drive controller, make sure to try them first. Furthermore, when using SAN make sure to use the most efficient virtual network controller along with network drivers for the Guest OS.

ALWAYS CONDUCT PERFORMANCE BENCHMARKING

Lastly, conduct performance tests with Microsoft Dynamics GP and SQL Server installed in the exact virtualization environment where the system is planned to be deployed. Macros in Dynamics GP can be utilized to perform load tests. This will ensure that performance issues are identified ahead of time and resolved prior to Go Live.

This article is a follow up to:

How to Improve Microsoft Dynamics GP Performance on Virtualized SQL Server