none
Question regarding planning for Hyper-V environment running SQL as a virtual machine

    Question

  • We need to upgrade accounting application currently running on SQL 2000 to a new server and SQL 2008R2.

    Our consultant suggested not to run SQL in virtual environment.  I ordered the enw hardware assuming that I would just run SQL as a physical machine. The hardware is Dell PowerEdge R610 with 6 core CPU, 12 GB RAM, RAID1/RAID 5 (2x146GB SAS, 4x300GB SAS drives).

    Just as I ordered the server, SQL 2012 with the new licensing was released, leaving me with two options - license SQL 2012 for all 6 cores (outside of my budget), or run SQL as a virtual system only utilizing 4 cores (within my SQL licensing budget). We are going to downgrade to SQL 2008 R2 becaue our applications are not 2012 compatible, but SQL 2012 is the only version available for volume licensin.

    I am considering running the SQL server as a Hyper-V server on this new hardware. For now, it would be the only VM on the server, but I would consider adding more VMs later on (most importantly secondary DC and a web server). Our old SQL 2000 server is running on a 7 year old Dell hardware with 2GB RAM and we don't have any performance issues, so there is extra power on the new server.

    Here are my questions:

    - Is it advisable to install Server 2008R2 as the host OS, enable Hyper-V role, and run guest Server 2008 R2 and SQL 2008 R2 in the VM? Our consultant recommends against running Hyper-V on a full installation of Server 2008 R2 because of unspecied issues. I researched it and asked around, and the only reason NOT to do that seems to be increased surface area on the host OS (= more things to patch), and some performance hit. On the other hand, we would be losing functionality and ease of management running Hyper-V Server (the free version). Restarts for patching are manageable in our environment and we are not going to push performance limits on the server anyway. I would like to do what the consultant doesn't recommend. Would I be setting myself up for a future failure having full install OS and also running the VM on the same server?

    - I planned the RAID1/5 configuration so that I can have system + logs on RAID 1 and databases on RAID 5. Our databases are only 15GB. How should I approach this with the VM? Host OS + VM OS + SQL logs on RAID 1 volume, and databases on RAID 5 volume? Is this possible?

    - What would be the best page file configuration?

    - We use Acronis to do image backups of the current SQL 2000 server to USB drive (in addition to other forms of backup). Can I still do backups on the VM to USB in the same fashion?

    - Is there anything else that I should be aware of? We are going to run production SQL2000 and test SQL 2008 R2 for the accounting application in paralel until the accounting department is comfortable with the upgrade. That means that we can change things few weeks into the test if necessary. Anything to be watching for?

    Thank you very much for any insight.

    Pavla

    Monday, April 09, 2012 5:58 PM

Answers

All replies

  • Hi,
     
    Please have a read of the following gudies.
     
     
     

    Vincent Hu

    TechNet Community Support

    Tuesday, April 10, 2012 3:12 AM
    Moderator
  • Vincent,

    Thank you for your reply. That answers the first question - it is just fine to run SQL on Hyper-V :-)

    How about the notion that it is a bad idea to run Hyper-V as a role on a full install of OS? I just have hard time believing that should cause any problems, but the consultant said that they started implementing Hyper-V that way and are getting all of their clients off that configuration because it is nothing but trouble (no specifics).

    Lastly - can you suggest the hard drive configuration? We don't have shared storage, our DBs are small and the plan is to host them on direct attached storage. Is the separation of logs and DB files still a valid consideration for virtualized SQL?

    Thank you again, I really appreciate your help.


    Pavla

    Tuesday, April 10, 2012 3:20 PM
  • Avoid using the parent partition, or the host OS. Just use it for Hyper V role for VM.

    Look from 12:34 to 14:15

    http://channel9.msdn.com/Events/TechEd/NewZealand/2010/SVR313 

    Plus, you will have licenses issues. If you use the host for anything else than manage VM, you will have to spent 1 Win license for the host.

    "If you are using your maximum ‘free allowance’ of Windows Server VMs on a host (1 in this case), you must use the host machine to purely manage the virtual machines running on it, and nothing else."

    http://blogs.technet.com/b/mattmcspirit/archive/2008/11/13/licensing-windows-server-in-a-virtual-environment.aspx?PageIndex=2

    So, I recommend to not use it.

    Wednesday, April 11, 2012 3:37 PM
  • Alexito,

    Those were some great links, thank you. The blog has the best explanation of MS virtual licensing that I have come across.

    The second link suggests that  I would get more of a performance hit using the RAID 1 volume (with parent partition => system drive for host OS) also for Hyper-V, than not separating SQL logs and databases? Do I understand correctly?

    Thanks again!


    Pavla

    Wednesday, April 11, 2012 5:44 PM
  • Yes, it's always best to keep the host OS and VM on different drive spindles.

    In general, the biggest performance hit comes from disk contention (not CPU or RAM).

    You'd probably want a RAID1 for the host OS and put your VM on a separate RAID volume or volumes.

    Wednesday, April 11, 2012 10:40 PM
  • We need to upgrade accounting application currently running on SQL 2000 to a new server and SQL 2008R2.

    Our consultant suggested not to run SQL in virtual environment.  I ordered the enw hardware assuming that I would just run SQL as a physical machine. The hardware is Dell PowerEdge R610 with 6 core CPU, 12 GB RAM, RAID1/RAID 5 (2x146GB SAS, 4x300GB SAS drives).

    Just as I ordered the server, SQL 2012 with the new licensing was released, leaving me with two options - license SQL 2012 for all 6 cores (outside of my budget), or run SQL as a virtual system only utilizing 4 cores (within my SQL licensing budget). We are going to downgrade to SQL 2008 R2 becaue our applications are not 2012 compatible, but SQL 2012 is the only version available for volume licensin.

    I am considering running the SQL server as a Hyper-V server on this new hardware. For now, it would be the only VM on the server, but I would consider adding more VMs later on (most importantly secondary DC and a web server). Our old SQL 2000 server is running on a 7 year old Dell hardware with 2GB RAM and we don't have any performance issues, so there is extra power on the new server.

    Here are my questions:

    - Is it advisable to install Server 2008R2 as the host OS, enable Hyper-V role, and run guest Server 2008 R2 and SQL 2008 R2 in the VM? Our consultant recommends against running Hyper-V on a full installation of Server 2008 R2 because of unspecied issues. I researched it and asked around, and the only reason NOT to do that seems to be increased surface area on the host OS (= more things to patch), and some performance hit. On the other hand, we would be losing functionality and ease of management running Hyper-V Server (the free version). Restarts for patching are manageable in our environment and we are not going to push performance limits on the server anyway. I would like to do what the consultant doesn't recommend. Would I be setting myself up for a future failure having full install OS and also running the VM on the same server?

    - I planned the RAID1/5 configuration so that I can have system + logs on RAID 1 and databases on RAID 5. Our databases are only 15GB. How should I approach this with the VM? Host OS + VM OS + SQL logs on RAID 1 volume, and databases on RAID 5 volume? Is this possible?

    - What would be the best page file configuration?

    - We use Acronis to do image backups of the current SQL 2000 server to USB drive (in addition to other forms of backup). Can I still do backups on the VM to USB in the same fashion?

    - Is there anything else that I should be aware of? We are going to run production SQL2000 and test SQL 2008 R2 for the accounting application in paralel until the accounting department is comfortable with the upgrade. That means that we can change things few weeks into the test if necessary. Anything to be watching for?

    Thank you very much for any insight.

    Pavla

    RAID5 is a write pig and pain in the butt to recover (does not survive double drive faults quite often happening during array rebuild). Go for RAID10 is possible.

    -nismo


    Friday, April 13, 2012 10:51 AM
  • Thanks guys for all the help, I am working through this.

    Our SQL is not under a big load. It is running an accounting app with max of 25 concurent users, and and IT Trackit software with maximum of 5 concurent users. I double checked the DB sizes and they are 0.5 GB and 3GB for the accounting app, and about 1GB for Trackit (that one is going to grow faster).

    All my drives are 10k SAS drives. I can't make it RAID 10 because all 6 drive slots are already occupied. I would do that the next time around.

    Considering all of that, do you think I am going to be OK putting parent partition on RAID 1 and the entire VM on the RAID 5?

    Also, we make frequent SQL backups to an external USB drive right now. I remember that Hyper-V couldn't recognize USB drives at first and needed Ethernet to USB device to address is that way. Is that still the case? If yes, what woudl you recommend there?

    Pavla


    Pavla

    Tuesday, April 17, 2012 3:08 PM