Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Virtualized SQL Server

Virtualized SQL Server

  • Comments 10
  • Likes

 

[Note the information in this post is now way out of date, so please refer to my post here instead..

http://blogs.technet.com/b/andrew/archive/2008/11/08/sql-server-2008-at-teched.aspx

Thanks Andrew]

I get more and more questions about SQL Server running in a Virtual machine so here’s 3 FAQs to start with…

1. Is there Support for SQL Server on non Microsoft Virtualization platforms (e.g.VMWare)? The definitive document on the is here.  In summary Microsoft doesn’t test this or support it, unless you have premier support in which case “Microsoft will use commercially reasonable efforts to investigate potential issues with Microsoft software running together with non-Microsoft hardware virtualization software”.

Of course the supported versions SQL Server (i.e.not 2000) and other Microsoft applications (Exchange, SharePoint etc.) will be fully supported on Hyper-V when Hyper-V is released. 

2. How does licensing work?  The exact terms are in this white paper, and in summary

  • For processor licensing each physical or active virtual machine running SQL Server must have a processor license (the number of chips not the number of cores) for each processor the virtual machine uses.  However if you have licensed enterprise edition for all of the processors on a physical machine you can run any number of SQL Server virtual machines on that box.
  • For CAL licensing each physical or active virtual machine requires the server license.  The exception to this is enterprise edition which just needs to be licensed per physical machine

Note that the licensing applies to SQL Server 2008 as well and to running SQL server on Microsoft’s Hyper-V platform in Windows Server 2008.

3. Performance. This is very easy to answer but not very helpful… Microsoft can’t test and publish how well SQL server performs on VMWare for example so I simply don’t know.  On Hyper-V this can be done as soon as Hyper-V is released.  There is always going to be some loss of performance and this will be the price you pay for improved manageability.

Finally don’t be put off by Virtual PC or VMWare demo’s you may have seen on laptops, you should get a better virtual experience using any virtualisation platform in a production environment, but the performance loss is going to vary depending on the physical infrastructure (SAN, number of Cores RAM etc.) and the workload you are virtualizing.

 
Update:
Since writing this post Microsoft has introduced the Windows Server Virtualization Validation Program (SVVP).  Essentially this means that the currently supported versions of SQL Server are now fully supported on Hyper-V, VMWare and other leading virtualisation platforms as part of this program.
Comments
  • "SQL Server Performance in a VMware Infrastructure 3 Environment" is located at http://www.vmware.com/files/pdf/SQLServerWorkloads.pdf

    The benchmark seem un-realistic as only one VM is running at a time, when the expected deployment is multiple VMs running simultaneously.  

    What is needed for the benchmark:

    On the hardware, run the benchmarks with Windows 64byte with multiple SQL Server instances, that is, no VMWare.

    Run the same benchmarks on the same hardware with VMware ESX Server systems and 2, 4 and 8 VMs

    Comments ?

  • We run several SQL 2000 and 2005 instances under VMWare Server on a couple of hosts. Using MS SQLIO simulator on the host vs inside a VM on a RAID10 SAS dual-quadcore host, I'd say we are getting about 80% of the potential disk I/O ability of the host. This kind of ratio also held true under older SCSI RAID host servers tested.

    This 80% is an acceptable trade-off to us for the manageability and ease of portability of the VM to other hosts. Stability has been excellent.

    In a purely performance-driven environment,of course, I'd not run it virtualized.

  • When one chooses to store data one of the primary goals is to access the data quickly and return the result set in a acceptable time frame. So one major goal is performance throughput. I still find it hard to understand that some engineers believe that SQL server in a VM outperforms "physical" SQL servers , and TG's comments backup up the point quite well to an extend. `if you except the performance loss then it is fine but when you don't then don't even consider it. `i am still new at  the VM realm and do not believe to run high end SQL server platforms in a VM environment .. maybe my mind will change or be forcefully changed one day but till then starting to talk about SQL servers processing 1000's of batches per second the only virtual component in our environment is the cluster name :-)

  • Wynand

    Since I wrote this post the SQL CAT team have produced a whitepaper on running SQL Server over hyper-V, http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SQL2008inHyperV2008.docx.

    one of the scenario is four vm's all running SQL Sevrer on one physical box.  

    Clearly this isn't the right approach for high performance vldb environements but how may sql servers are like that of the hundreds of thousands out there, and how manby could be rationalised in some way either by using instances or through virtualisation.

  • Applications and servers (such as SQL Server) are supported when running within operating systems which pass the SVVP requirements in a third-party virtualization software product.  VMWare’s ESX Server is considered a SVVP qualified virtualization product when running Server 2000 SP4, 2003 SP2, or 2008.  The supporting reference is below:

    http://www.windowsservercatalog.com/results.aspx?&bCatID=1521&cpID=0&avc=0&ava=0&avq=0&OR=1&PGS=25&ready=0

  • Brian

    Thbnaks for this this post is past it's sell by date and I have an updated post ..

    http://blogs.technet.com/andrew/archive/2008/11/08/sql-server-2008-at-teched.aspx  

    which echoes your point

    Andrew

  • Gents, some info please? In a BI environment, will it be best to use a SQL cluster than VM the envirenment? What if more resources could be thrown to the VM to get same performance?

    Regards

  • Gents,

    in a BI environment, will it be bets to go cluster or VM, even if you can increase resources to the VM so performance doesn't become an issue, as stated above. Microsoft suggested cluster, but no reason up to date supplied??????

    Regards

  • Faniel,

    As far as I know we have no recommendation or best practice at the moment on virtualising the BI components of SQL Server.  

    However It will depend on what you want to do with your BI, such as move it quickly from one server to another, make it highly available, load balance it etc..

    I would also need to know what you mean by BI, is it just the warehouse, is it anlysis services , reporting services or what?

    Andrew

  • We currently have a situation where we have low performance in a virtual environment. Lot's of causes possible and one is SQL Server running virtual. I personaly don't trust this much. I think (my feeling says) that unless the environment is tuned optimal the performance loss must be significant.

    I am not an expert on this at all, but strange enough I don't find straight answer on internet although many experts seem to be out there. I's a difficult subject and situations can differ much but still this is strange. To find a way out I look at it from the other side.

    Some parties advise to use a physical server with a RAID 10 configuration (combining the speed of RAID 0 and the reliability of RAID 1), with enough memory and a good processor. Not being an expert on this, it makes a reliable impression. A maximum of performance must be the result.

    Now, how on earth can this performance be deliverd in a virual environment. As said, maybe it is possible to come close when a solid effort is done in tuning, buying the right hardware with enought memory etc. But in general budgets are limited so the virtual environment will not be optimal.

    So my opinion for RDBS'es in virutal environments, is that in average situations the performance loss is considerable compared to fysical environments.

    Any opinion? Anyone followed this kind of considerations and went for fysical without even trying virutal for example?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment