Running SQL Server in a Virtual Server Environment

Server virtualization is a hot topic. Most of the new server installations I perform for my consulting clients have some type of virtualization. SQL Server is unique because of the potential processor, disk, and memory demands it can place on hardware. SQL Server is one of the few Microsoft server applications that has the potential to place a significant load on the processor, especially if your application deals with large tables or high transaction volume. Therefore, running SQL Server in a virtual environment is an option many enterprises will want to consider.

Related: Essential Tips for Virtualizing SQL Server

Physical vs. Virtual

Most SQL Server installations don’t use 100 percent of a server’s resources all the time. In fact, many SQL Server applications use only a percentage of a server’s processing potential. SQL Server processor utilization tends to be high for short periods of time. The current version of servers that are available with quad-core processors and significantly faster Serial Attached SCSI (SAS) drives capable of 3Gbps transfer rates represent a quantum leap in server performance. Virtualization can take advantage of the processing power of this new generation of servers, providing significant leverage for your hardware dollar.

Physical server advantages. Traditionally, SQL Servers run on dedicated hardware. This gives the server full access to all the processing power on the server. Running SQL Server on a physical server means that the server doesn’t need to compete with other virtual servers for resources, and a physical server will perform approximately 10 percent to 25 percent faster than a virtualized server on the same hardware. SQL Server 2005 Standard and Enterprise versions can address the maximum amount of memory that the server and OS will support. Also, a hardware failure on the server will affect only one SQL Server installation.

Physical server disadvantages. A physical server can waste resources because the server’s processing power often goes unused. Using one physical server per SQL Server installation significantly increases your hardware costs, and if each physical server is placed on a service contract, your maintenance costs are also higher. And the greater number of physical servers consumes more electricity and requires more physical space than a virtual server setup does.

Virtual server advantages. By consolidating several virtual server guests onto a single physical server, you can save a significant amount of hardware cost and provide better utilization of hardware resources. When determining where a virtual guest should reside, pick a physical server that hosts complementary guests. For example, if a SQL Server installation will place a significant amount of I/O stress on the processor, run it on a physical server that hosts other virtual guests that don’t require the same type of I/O processing.

Virtual servers can lower your hardware maintenance costs because you’ll have fewer physical servers to maintain, as well as lower electric bills and power requirements. With the cost savings, you might be able to place all your host servers on a 24x7 support contract compared with a 8x5 support contract if you’re not using virtual servers. Even with this increased service level, you’ll likely have a net savings for hardware maintenance. Virtual servers also make bare metal restores easier. Every virtual server hardware configuration is consistent regardless of the host’s physical hardware configuration. Therefore, you simply copy the virtual server guest files to another physical host.

Virtual server disadvantages. With VMware’s VMware Server and Microsoft Virtual Server 2005, you can allocate a maximum of approximately 3.8GB of memory to each virtual server guest. With VMware ESX Server, you can allocate a maximum of 16GB of memory to a virtual server guest. If your SQL Server deployment requires more memory than these limitations, the server is probably not a good candidate for virtualization. Because all virtual server guests must share the host server’s physical hardware resources, if too many guests are running on a host server, performance will suffer. Avoid placing too many guests on one host or guests that require concurrent hardware resources.

Fault tolerance is another virtual server disadvantage. A hardware failure will take down all guests running on the host. However, if you use VMware Server or Virtual Server 2005 and you have a backup of each virtual server’s guest file, you can restore these files onto a different host and quickly bring up the failed guests.

Virtual Guest Placement

When planning the placement of your virtual servers, try to select virtual server guests that have hardware resource requirements that are complementary to each other. For example, a SQL Server application that contains a large number of stored procedures and triggers with a relatively small database with light transaction volume will have higher processor requirements, but relatively light disk requirements. This SQL Server instance could be run in the same host as a file and print server that stresses the processor very little but may require high disk performance to serve files. Take into consideration how many users will access each virtual server and how these servers are stressed during the day. For fault tolerance, don’t place all of your most important servers on a single host. If your server environment is complex (1000+ servers) you might want to consider one of the server metric and design tools such as CiRBA. This tool helps automate the process of server placement, host design, and performance tuning of virtual server guests and hosts.

Creating Virtual Server Guests

I suggest that you create a base image for each type of server guest you plan to use. The “Creating a Virtual Server Base Image,” checklist shows the basic guidelines I follow when building a virtual server base image. After you create the base image, you can create a new virtual server guest by following these steps:

If you’ve properly created your base images, it should take you about 15 minutes to bring up a new server. You can then install SQL Server or other applications you might need. Ideally, you should place these images on a SAN or other file server share so that all new hosts have convenient access to them. Consider using ISO images of the installation CD-ROMs of SQL Server (and other applications) to speed up the installation of new servers. Properly configured base images can save you an hour or more of set up time per server—and much more if you consider any required hardware installation.

Hardware Configuration Tips

The latest generation of servers, with quad-core processors and SAS hard drives, represents a quantum leap forward in processing power, which makes them ideally suited for virtual server hosts. Use the following virtual server host hardware configuration tips to get the best performance from your virtual server guests.

Host server memory. Install adequate memory for your host server and virtual server guests. Most of our virtual server host servers start out with at least 8GB of memory. Make sure that the host OS has the capability of addressing all the physical memory in the server. The standard 32-bit version of Windows 2003 can address only 4GB of memory, but the standard x64 version of Window 2003 can address 32GB of memory.

Host OS. If you plan to use Windows Server as the host OS, consider installing the x64 version of Windows 2003. Windows 2003 Standard x64 supports 32GB of memory and Windows 2003 Enterprise x64 supports 1TB of memory. The x64 versions are mandatory if you want to run any x64 virtual server guests, but even 32- bit guests will realize some performance gain by using Windows 2003 x64 as the host OS. Today, only VMware Server and ESX Server support x64 guests; Virtual Server 2005 supports only 32-bit virtual server guests.

Processor. Use quad-core processors for the best virtual server performance. Quad-core processors are typically about 50 percent faster than the equivalent dual-core processor. If you plan to run any x64 virtual server guests, make sure the processor is capable of x64 processor virtualization. Processors that support x64 virtual guests include AMD Athlon 64, revision D or later; AMD Opteron, revision E or later; AMD Turion 64, revision E or later; AMD Sempron 64-bit capable version, revision D or later (experimental support); and Intel Extended Memory 64 Technology (EM64T) Virtual Technology (VT)-capable processors (experimental support). If you’re using Intel processors, you’ll need to enable the virtualization of the processor in the BIOS of the host server before you can install any x64 virtual server guest of the host server before you can install any x64 virtual server guest.

Host hard drive configuration. The same basic disk configuration rules apply for a virtualized SQL Server system as for running SQL Server on a physical server. To configure a host to run SQL Server, set up the hard drives with at least one RAID 1 array for the SQL Server transaction logs and either a RAID 5 or RAID 10 array for the data portion of the SQL Server database. For larger SQL Server installations, it’s a good idea to separate the transaction log and the data portion on separate RAID arrays. SQL Server transaction logs are written to sequentially and perform best when they reside on a RAID 1 array, which gives the best performance for sequentially written files. RAID 5 or RAID 10 arrays give the best performance for randomly accessed files and are a good choice for the data portion of any SQL Server database. For the best performance, run the x64 bit version of SQL Server 2005. If you anticipate heavy disk activity from your virtual server guests, consider the host server disk configuration that Table 1 shows. Using this configuration, the host server might have 10 physical hard drives—two drives for the first RAID 1 array, two drives for the second RAID 1 array, and six drives for the RAID 10 array. Use SAS drives in the server and avoid using Serial ATA (SATA) or IDE drives.

Virtual server guest add-ins (i.e., VMware Tools or Microsoft Virtual Machine Additions). Installing these add-ins should be one of the first steps you complete after the virtual server OS installation. Doing so will significantly improve the overall performance of any virtual server guest.

SCSI drives for the virtual server guests. When you configure the disk type of the virtual server, always use SCSI disks. This is independent of the type of hard drives you’re using on the host.

Virus scanning. On the host server, disable virus scanning of all virtual server guest files.

Backup Strategies

Backing up virtualized SQL Server implementations presents some unique challenges and benefits compared with backing up SQL Server servers that are running on a physical server. With mission critical SQL Server applications, I typically perform a full daily backup of the SQL Server with either differential or incremental hourly log dumps to tape or disk during the business day. But one of the main advantages of a virtualized SQL Server is the ability to obtain an image backup of the SQL Server by simply backing up the virtual guest files on the host server. If you’re running ESX Server you can perform a backup of any virtual server guest by using VMware’s Consolidated backup or Vizoncore’s esxRanger software while it is still running.

If you’re running Virtual Server 2005 or VMware Server, you must shut down the virtual server guest before you can obtain a backup of it. Fortunately, you can write a script to automate the process of taking down the virtual server guests, running a backup of the host, then restarting the virtual server guests. I usually configure the backup to obtain an image backup of all virtual server guests once a week.You can find more information about virtual server backup strategies from "Backing Up virtual Server Guests."

Disaster Recovery

In a disaster recovery scenario, you can replicate these virtual server image files to a remote disaster recovery location. You could use the built-in feature of Windows 2003 R2’s DFS Replication (DFSR) to automatically replicate these files to a remote location. From week to week, the virtual server image files don’t change much so these types of files work well with DFSR because they can take advantage of the Cross-File Replication and Remote Differential Compression (RDC) features included with DFSR. The Cross-File Replication feature uses information from local files to duplicate files in a remote location. The RDC feature will replicate only changes to an existing file, greatly speeding up the replication process. So even though virtual server guest files tend to grow rather large, you can replicate them relatively quickly by using DFSR technology.

Even with the image backup, I still use the traditional back up method so that I can perform a granular restoration of files or databases. Although the image backup is great for moving a virtual server from one host to another host, it’s not the best solution when you want to restore a single file or portion of a transaction log.

A Virtual Test Drive

Running virtualized SQL Servers definitely has a learning curve, but the benefits are tremendous. Start off slow, with perhaps a test server or low profile SQL Server until you become familiar with the technology.