• subscribe
Search
Browse By: Author | Issue
November 20, 2007 12:00 AM

Running SQL Server in a Virtual Server Environment

Optimize server performance with these virtualization tips
Alan Sugano
SQL Server Pro
InstantDoc ID #95799

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 at www.windowsitpro.com/Article/ArticleID/95309/95309.html.

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.



ARTICLE TOOLS

Comments
Add A Comment
  • Diana
    5 years ago
    Dec 21, 2007

    Be sure to watch for our product showdown on virtualization software ii mid-2008. We'll show you how the products stack up against each other.
    Diana May
    SQL Mag

  • Alan
    5 years ago
    Dec 17, 2007

    Wavel,

    Let's say you're really small company that only has a budget for a single server, but you need to run a SQL server 2005 x64, Exchange 2007 (requires x64), Terminal Server and a File Server. SQL and Exchange need x64, but you need the Terminal Server and File Server to run 32 bit for compatibility. One option is to purchase four servers (or maybe two but you would have to combine SQL and Exchange, File and Terminal Server. Possible, but not the ideal situation especially for the File and Terminal Server), but if you don't have the budget for that, you could get a larger server and run four virutal guests on it. You might have some money left over to put the host server on a 24x7 4 hour service contract.

    To answer your patching questions, Blue Lane makes a in-line security product that works with VMware ESX. It will essentially "patch" all of the virtual guest running on a host running the Blue Lane product. Check out this link http://www.bladewatch.com/2007/03/15/blue-lanes-vmware-security-product/ for more info.

    But, it sounds like you're already made up your mind, so probably running everything on dedicated hardware is your best bet.

  • RICK
    5 years ago
    Dec 13, 2007

    Alan:
    Your statement that the biggest advantage is security is interesting. Assume that the Sharepoint server is compromised. What assures you that there is no possible way of getting to the files on one of the other virtual servers? Or even better: DOWNLOADING the virtual image for those servers!

    You also said that you could create a fully patched instance in 15 minutes once you had your base image. But MS is patching monthly, so do you end up with new vm's that still need patching? Or are you updating the base file each month? And once your new vm is running, it needs to be patched seperately from all the other vm's right?

    As for testing in a vm that's running on a production machine.... really?? Are you 100% sure that you can't crash the hardware inside the vm? Seems pretty risky to me.

    The point I'm trying to make is that so far, I'm not seeing a good reason to run a vm. We are a very small company - every server we buy affects our bottom-line, so it's not an issue of us being a fortune 1000 that can buy as much hardware as we desire :) Every point you've made as a reason why you'd want to run virtualization, seems to have a valid counter-arguement against it. I'm looking for the one big reason that makes me slap my head and say "Duh!".

  • Alan
    5 years ago
    Dec 12, 2007

    Wavel:

    Probably the biggest advantage in your situation is security. Assuming you need to run Outlook Web Access or Sharepoint, you can install a virtual server and only have that front end Web Server exposed to the outside. If you have everything installed on one machine and it gets hacked all of the data that resides on that server is vulnerable.

    Since virtualization creates a consistent platform, it makes recovery much easier. If you have the virtual server images backed up, all you have to do is restore the image onto a new server and you're done. If you're not virtualizaed, you have to load the OS, install the backup software, install SQL Server and then restore the databases.

    We've also found that running separate applications per server, reduces the possibility of conflict between applications. We've seen instances where corruptions in certain components can cause other items to fail on the server when they're all installed on the same server. For example, if the metabase in IIS is corrupted it can cause Exchange to crash.

    If you're doing anytype of testing, it's very easy to create a virtual test environment, and take it down without messing up your production environment. Once you have your base images installed, you can create a new server in 15 minutes, that's fully patched and ready to go. That's pretty difficult to do with a physical server.

    Even in small shops virtualization may have a place, espeically if you have to run 32 and 64 bit servers. I suggest playing around with the technology and start slow. If you're not sold on technology I think you will be after you've had a chance to experience it first hand.

    Hope that helps!

    Alan Sugano

  • Alan
    5 years ago
    Dec 12, 2007

    ge:

    If you're experiencing heavy disk I/O, you could move to the x64 platform, load up the server with a lot of memory and cache everything. This will take a siginficant load off of the disk subsystem. Of course you have to use ESX which supports an x64 guest, and now supports up to 64GB of memory on a x64 guest. This is the same strategy that Microsoft is using for Exchange 2007. Run x64, load the server with a lot of memory and cache everything.

    Thanks!

    Alan Sugano

You must log on before posting a comment.

Are you a new visitor? Register Here

© 2012 Penton Media, Inc.

Windows is a trademark of the Microsoft group of companies. Windows IT Pro is used by Penton Media Inc. under license from owner.