Answer


    • NOTE: See additional information for VMWare at the end of this article.


Cyrious' answer is that you can, but you should not. Unless you have a very high-end (expensive) version of SQL Server, your performance may be dramatically worse. The reason you should not run SQL on a virtual machine is not really technical - it is a combination of the way processors are handled in virtual machines and the licensing that applies to every version of SQL Server.[1]

Each version of SQL Server (except the Enterprise version) is restricted so it will only run on a certain number of processors. Microsoft SQL Server Express, the default for new and smaller users, is restricted to 1 physical processor (a single chip) and up to 4 cores. Most modern processors have several cores, and a server computer would typically have at least 4 cores. A core can be thought of as a separate processor that works closely with others in the same chip. Each core can also enable hyper-threading, which splits its power among two different "virtual processors".

On a Physical Machine (PM), not using any Virtual Machine Technology

A quad-core processor with hyper-threading enabled has 8 virtual processors on a single chip, all of which SQL Server Express can use because SQL Express supports 1 physical processor, 4 cores. For businesses with only a few users, this is normally plenty of processing power.
SQL Server Limits - Physical Machine.png

On a Virtual Machine (VM)

However, with a virtual machine (VM) something different happens. In a VM, all of the underlying hardware is removed from direct view. What the VM sees and can access are all controlled by what the physical machine (PM) and VM Server technology reveals. The VM has no direct access to the hardware.[2]

In this case, each virtual processor on the PM is "presented" to the VM as if it were a real processor. This doesn't change the processing power, so it normally doesn't affect performance. However, because of the licensing restrictions for Microsoft SQL Server, this results in a dramatic reduction in the real processing power SQL will use. In practice, it may result in a reduction between 1/4th and 1/16th of processing power available to SQL.

SQL Server Limits - Virtual Machine.png

Such a reduction in processors available to SQL can result in dramatic slowdowns and problems. For this reason, unless you are running the Enterprise version of SQL (which may cost you tens of thousands of dollars), Cyrious strongly recommends you not put SQL Server on a virtual machine.

Other Versions of SQL

The example above showed the reduction for SQL Express, but a similar problem exists for all versions of SQL Server except Enterprise.

The processor limits for other versions of SQL Server 2012 are:

Version
Limit
Express
lesser or 1 Processor or 4 Cores
Standard
lesser of 4 Processors or 16 Cores
Enterprise
No limit[3]


Control on Virtual Machines

There is no inherent problem with running Control on a virtual machine, however the added layer of the virtual machine will always result in a slight performance loss when compared with a physical machine. Running and maintaining a virtual machine environment requires trained IT skills. There is an added layer of complexity and performance tuning/maintenance that is required to effectively deploy a Virtual Machine environment. Cyrious does not recommends this configuration for any customer without a trained and certified staff (internal or external).

Notes on VMWare


With VMWare ESX 5.x the default is 1-core per CPU, you can alter the number of cores given to a CPU. This will overcome many of the problems described here by allowing SQL Express to use the extra processing capabilities normally found in a CPU. For more information, see http://pubs.vmware.com/vsphere-50/index.jsp?topic=%2Fcom.vmware.vsphere.vm_admin.doc_50%2FGUID-73B63A2C-96D4-4C14-80A3-3A698DC48F06.html.

Note: Configuring a VMWare server is not something the Cyrious technicians are trained or permitted to do. Your network administrator will need to perform this function her/himself.


Version Information

  • Entered : 01/2010
  • Updated: 06/2014
  • Version(s) : All versions of Control.

See Also


  1. ^ See http://msdn.microsoft.com/en-us/library/ms143760(v=sql.110).aspx for Microsoft's (very confusing) page on this subject.
  2. ^ In practice, some direct access is used for coordination. But in practice, the VM should not know the details of the PMs hardware.
  3. ^ Many VMs limit the number of processors they support, so even if SQL Enterprise is installed often not all the processors can be made available to the VM.