Lot of times, when stepping up for supporting a new SQL Server Instance, we as a DBA need to know whether it’s running on a Physical Machine or a VM. In this post, I will provide you guys with a simple T-SQL Script which you can run from your client machine(SSMS) connecting to the respective SQL Server, which lets you know if it’s a Physical machine or a VM.
Note: This Script works only on 2008R2 SP1 and above.
SELECT SERVERPROPERTY('computernamephysicalnetbios') AS ServerName ,dosi.virtual_machine_type_desc ,Server_type = CASE WHEN dosi.virtual_machine_type = 1 THEN 'Virtual' ELSE 'Physical' END FROM sys.dm_os_sys_info dosi /* If you have a CMS configured, run the below Script from your CMS against multiple servers*/ SELECT dosi.virtual_machine_type_desc ,Server_type = CASE WHEN dosi.virtual_machine_type = 1 THEN 'Virtual' ELSE 'Physical' END FROM sys.dm_os_sys_info dosi
“virtual_machine_type_desc” Output value description
NONE – SQL Server is not running inside a virtual machine.
HYPERVISOR – SQL Server is running inside a hypervisor, which implies a hardware-assisted virtualization. If the instance is running on the host OS, the description will still return HYPERVISOR.