how to find a Server .. IS Physical machine or Virtual Machine

Question

how to find a Server .. IS Physical machine or Virtual Machine

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Leon . Orlov . Ten Centuries Points: 1212 More actions · Answer 1

Here is snippet of PowerShell script that returns some details like socets, cores, memory, and tells if it is VM or not (for only VMWare hosts).

$Servers = Invoke-sqlcmd2 -ServerInstance "<dbserver>" -Database "SUPERMASTER" -Query "SELECT COMPUTER_NAME FROM V_WIN_COMPUTERS"

foreach ($s_ in $Servers ) {

$processors = get-wmiobject -computername $s_.COMPUTER_NAME win32_processor

if (@($processors)[0].NumberOfCores) {

$cores = @($processors).count * @($processors)[0].NumberOfCores }

else {

$cores = @($processors).count

}

$sockets = @(@($processors) |

% {$_.SocketDesignation} |

select-object -unique).count;

$comp_name = $s_.COMPUTER_NAME

$memory = get-wmiobject -computername $comp_name Win32_PhysicalMemory

$TotalCap = 0

Foreach ($stick in $memory) {

$TotalCap = $TotalCap + $stick.capacity/1mb

}

$info = get-wmiobject -computer $comp_name Win32_ComputerSystem

$manufacturer = $info.Manufacturer

if ($manufacturer -match "VMware") { #I know that I only have VMware VMs.

$vm = 1}

else {

$vm = 0

}

Write-Host "Machine= $comp_name Cores= $Cores CPU_SOCKETS= $sockets PHYSICAL_MEMORY= $TotalCap Manufacturer= $manufacturer VM= $vm "

}

have fun,

~Leon

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Here is an article just went live today. I would have posted sooner but the article was scheduled for today 😉

http://jasonbrimhall.info/2012/03/07/physical-or-virtual/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

agozzo Grasshopper Points: 19 More actions · Answer 3

[font="Verdana"]This is how I did it, must be another way, but at least this one works:

IF OBJECT_ID('TEMPDB..#SYSTEMINFO') IS NOT NULL

DROP TABLE #SYSTEMINFO

CREATE TABLE #SYSTEMINFO

(SYSTEMINFO VARCHAR(500))

EXECUTE ('INSERT #SYSTEMINFO (SYSTEMINFO)

EXEC xp_cmdshell ''systeminfo''')

IF (SELECT COUNT(*) FROM #SYSTEMINFO WHERE SYSTEMINFO LIKE '%VMWare%') > 0

SELECT @@SERVERNAME AS 'SERVER_NAME', 'YES' AS 'VIRTUAL_MACHINE'

ELSE

SELECT @@SERVERNAME AS 'SERVER_NAME', 'NO' AS 'VIRTUAL_MACHINE'[/font]

dwithroder SSC Eights! Points: 999 More actions · Answer 4

when i select @@ version or

use sys dm_os_sys_info for the virual_machine_type

i get one result telling me the server is virtual

but if i run systeminfo at command prompt or

use powershell like Get -WmiObject -Class Win32 _ComputerSystem -NameSpace "root\CIMV2" -property "Model"

on the same server i get a different result telling me the server is not virtual

the facts. it is not a virtual server

any thoughts why the results would be different?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

dwithroder (4/5/2012)
when i select @@ version or
use sys dm_os_sys_info for the virual_machine_type
i get one result telling me the server is virtual
but if i run systeminfo at command prompt or
use powershell like Get -WmiObject -Class Win32 _ComputerSystem -NameSpace "root\CIMV2" -property "Model"
on the same server i get a different result telling me the server is not virtual
the facts. it is not a virtual server
any thoughts why the results would be different?

Are you running SQL Server 2008 R2 sp1?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

dwithroder SSC Eights! Points: 999 More actions · Answer 6

dwithroder

SSC Eights!

Points: 999

April 5, 2012 at 2:18 pm

#1469820

yes 2008R2 SP1

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

If the dmv is misreporting that the machine is virtual - i would file a connect item.

In that case, I would rely more on the results of querying WMI direct.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

dwithroder SSC Eights! Points: 999 More actions · Answer 8

thanks for the super quick replies

yes, we are going to look away from the dmv for now

kind of scary though if you don't feel like you can trust one...

i've been trying to google when/how the dmv gets its info but not had much luck yet

Leon . Orlov . Ten Centuries Points: 1212 More actions · Answer 9

dwithroder: can you post the actual result for both inqueries... running on sql and wmi powershell.

dwithroder SSC Eights! Points: 999 More actions · Answer 10

version says

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

sys.dm_os_sys_info says

virtual_machine_typevirtual_machine_type_desc

1 HYPERVISOR

Get-WmiObject -Class Win32_ComputerSystem -NameSpace "root\CIMV2" -property "Model" says

Model : PowerEdge 2950

Matimba SSC Enthusiast Points: 136 More actions · Answer 11

declare @Virtualname varchar(255)

declare @Virtual varchar(255)

create table #OSinfo(OSinfo varchar (255))

insert into #OSinfo

exec master..xp_cmdshell 'systeminfo'

set @Virtualname =(select ltrim(SUBSTRING(OSinfo,CHARINDEX(' ',OSinfo),LEN(OSinfo)))

from #OSinfo

where OSinfo like '%system%' and OSinfo like '%manufacture%')

if (@Virtualname not like '%virtual%')

begin

set @Virtual='Not Virtual'

set @Virtualname='Not Virtual'

end

else

begin

set @Virtual='Virtual'

end

print @Virtualname

print @Virtual