Virtualizing Microsoft SQL Database server
that Microsoft has these optimized to work as good as a physical machine?
Yes, MS has - quite close.
The main problem is that it makes only sense in small installations. The moment your virtual SQL Server requires its own machine with a carefully laid out disk setup - which is any except a small SQL instance with low volume - there is nothing else to really put on that server.
I run a SQL server on a virtualization platform. Out of the 20 disks in that machine 12 are dedicated to the SQL server (for logs & data, including tempdb - the OS loads from a VHD). The next upgrade will take it quite to the limit - even now it is the fattest VM with 16GB RAM of my available 64GB. Once I need to upgrade that.... what sense does it have?
With current technology you are limited to 4 virtual cores - 16 in Hyper-V 3 (coming next year). This is not really a lot for databases analytics. If you do OLAP-type processing, visualization with Hyper-V may simply not scale up enough.
The main problem is thus not that MS can not get close to comparable hardware, but that SQL Servers can get so big that the comparable hardware means a 1 SQL Server on one hardware level anyway, plus you can not scale a VM as good as hardware, sadly.
Today's hypervisors can provide almost the same performance as the underlying physical hardware, as long as the performance is physically there and you don't put too many VMs on the same hardware; so, virtualization per se is not going to impact performance a lot, if you have enough CPUs/RAM and you make sure this VM's data disks are not shared with other VMs.
For a small/medium workload, this is almost always enough; but for large workloads, you'd really want to have SQL Server running on physical hardware, for two main reasons:
- Virtualized hardware can only scale up to a certain level, you can't have more than 4 virtual CPUs with Hyper-V and 8 with ESXi.
- If you are going to assign all the physical resources of a virtualization host to a single VM, you may as well have it run directly on the physical hardware and remove that (little) virtualization overhead.
Short answer: no VM, no matter what stack, can match the IO performance of a physical server.
Long answer: It really depends on load. If you're a company of 80 users, and your SQL server is serving an accounting application, a SharePoint instance, and some other random Line of Business application I doubt you'll see much performance hit.
If that SQL server is serving thousands of users, or has some intensive application running against with with thousands of queries and calculations/second, you'll see a massive performance hit.
I see no reason why you should not virtualize databases. Even if one SQL server takes a whole hypervisor...
Yes, virtualization always comes with some performance loss. However, that loss is getting smaller and smaller with every release and I believe that today, this weights little compared to all the other benefits you will get from virtualizing servers. Think about high availability and disaster recovery, the option to temporarily add public cloud resources to your infrastructure when you have a sudden raise in demand, easy deployment of new servers.
Also, I also see everybody mentioning vCPU limitations. Two comments: general rule of thumb; don't add more vCPUs to a VM then there are cores available on a certain host/hypervisor. So if your host has 8 cores, 8 vCPUs is all you are going to need. Second comment is that vSphere 5 Enterprise Plus supports up to 32 vCPUs per VM.