Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Last revision Both sides next revision
can_i_use_a_vm_virtual_machine_to_host_sql_server [2019/01/27 11:28]
127.0.0.1 external edit
can_i_use_a_vm_virtual_machine_to_host_sql_server [2019/07/02 11:52]
admin
Line 1: Line 1:
-   +Answer
- +
- +
- +
-   +
- +
- +
- +
-Answer  +
- +
  
 <html><div style="margin-left: 40px;"> **NOTE: See additional information for VMWare at the end of this article.** </div></html> <html><div style="margin-left: 40px;"> **NOTE: See additional information for VMWare at the end of this article.** </div></html>
  
- +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.See [[http://msdn.microsoft.com/en-us/library/ms143760|http://msdn.microsoft.com/en-us/library/ms143760]](vsql.110).aspx for Microsoft's (very confusing) page on this subject.
- +
-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.See http://msdn.microsoft.com/en-us/library/ms143760(vsql.110).aspx for Microsoft's (very confusing) page on this subject. +
- +
  
 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". 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
- +
-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. 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_width600
  
- +On a Virtual Machine (VM)
-{{::sql_server_limits_-_physical_machine.png_width600?nolink&|}} +
- +
- +
- +
-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.In practice, some direct access is used for coordination. But in practice, the VM should not know the details of the PMs hardware. 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.In practice, some direct access is used for coordination. But in practice, the VM should not know the details of the PMs hardware.
- 
- 
  
 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. 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_width600
- +
-{{::sql_server_limits_-_virtual_machine.png_width600?nolink&|}} +
- +
  
 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. 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
- +
-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 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: 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 limitMany 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.||
  
- +Control on Virtual Machines
-||~ Version ||~ Limit || +
-|| Express || lesser or 1 Processor or 4 Cores || +
-|| Standard || lesser of 4 Processors or 16 Cores || +
-|| Enterprise || No limitMany 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. || +
-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). 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|http://pubs.vmware.com/vsphere-50/index.jsp?topic%2Fcom.vmware.vsphere.vm_admin.doc_50%2FGUID-73B63A2C-96D4-4C14-80A3-3A698DC48F06.html]].
-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. 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
  
- 
-Version Information  
   * Entered : 01/2010   * Entered : 01/2010
   * Updated: 06/2014   * Updated: 06/2014
   * Version(s) : All versions of Control.   * Version(s) : All versions of Control.
  
 +\\