6

Is there a anyway to figure out via a SQL Script to know whether SQL Server is running on a Physical Server or Virtual Server ?

7

If you are using SQL Server 2008 R2 RTM or older then there is a CMD Command "SYSTEMINFO" which has information about System model.
OR
If cmdshell is enabled on instance then you can execute it from SQL Server Management Studio.

DECLARE @result int
EXEC @result = xp_cmdshell 'SYSTEMINFO'

System Manufacturer: VMware, Inc.
System Model: VMware Virtual Platform

New columns (virtual_machine_type, virtual_machine_type_desc) in DMV sys.dm_os_sys_info are introduced in SQL Server 2008 R2 Service Pack 1

http://blogs.msdn.com/b/sqlosteam/archive/2011/05/11/more-on-dmv-changes-in-sql-2008-r2-sp1.aspx

| improve this answer | |
3

I found a script:

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 
| improve this answer | |
0

Using Powershell use below..

$Virtual = Get-wmiobject win32_computersystem | Where-Object model -EQ 'Virtual Machine'
  if ($Virtual) 
  {
  Write-Host "SQL Server Running on Virtual Server" -ForegroundColor Green
  }
  else 
  {
  Write-Host "SQL Server Running on Physical Server" -ForegroundColor Yellow 
  }

Read more https://mkelsawy.blogspot.com/2019/09/DetermineHostedSQLServerUsingPowershell.html

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.