Determine if SQL Server is running on VM

Question

Post reply

Determine if SQL Server is running on VM

Express12

SSCoach

Points: 18518
More actions
August 23, 2011 at 5:41 am

#242081

I need to indicate our SQL Servers running on VM's and non-VM's. (for both Windows Server 2008 and 2003)
How can I make this determination?
BT

Viewing 15 posts - 1 through 15 (of 17 total)

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

Perry Whittle SSC Guru Points: 234040 More actions · Answer 1

which hyper visor are you using?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"

Express12 SSCoach Points: 18518 More actions · Answer 2

Express12

SSCoach

Points: 18518

August 23, 2011 at 7:48 am

#1372004

VMware

BT

GregoryF SSCertifiable Points: 6296 More actions · Answer 3

Express12 (8/23/2011)
I need to indicate our SQL Servers running on VM's and non-VM's. (for both Windows Server 2008 and 2003)
How can I make this determination?

Do you need to know just is the SQL Server Service is installed?

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Express12 SSCoach Points: 18518 More actions · Answer 4

We have over 150 instances of SQL Server installed on servers. I'd like to identify those instances installed on a VM and those installed on physical machines.. Spiceworks looks interesting. (spiceworks.com)

BT

Robert Klimes SSCoach Points: 17865 More actions · Answer 5

SELECT @@VERSION

This will tell you if instance in on hypervisor.

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

skrilla99 SSCertifiable Points: 5795 More actions · Answer 6

Express12 (8/23/2011)
We have over 150 instances of SQL Server installed on servers. I'd like to identify those instances installed on a VM and those installed on physical machines.. Spiceworks looks interesting. (spiceworks.com)

What local server rights do you have? Is xp_cmdshell enabled?

I'm sure there is a better way, but you could try something like:

exec xp_cmdshell 'systeminfo | find "System Model:"'

Even better if you can run this against a central management server to do them all at once.

-Dan B

skrilla99 SSCertifiable Points: 5795 More actions · Answer 7

Robert klimes (8/23/2011)
SELECT @@VERSION
This will tell you if instance in on hypervisor.

Bob - It looks like that does work for VMware also - if you are running on SQL 2008 R2.

Not sure about 2008 yet. Pretty sure it doesn't in 2005.

-Dan

skrilla99 SSCertifiable Points: 5795 More actions · Answer 8

As another option - this powershell script, found in the comments here:

http://blogs.metcorpconsulting.com/tech/?p=40

seems to work pretty well. It would need to be adapted to pull from your list of servers and include the server name in the output.

$strComputer = "serverName"

$colItems = get-wmiobject -class "Win32_ComputerSystem" -namespace "root\CIMV2" -computername $strComputer

foreach ($objItem in $colItems) {

write-host "Manufacturer: " $objItem.Manufacturer

write-host "Model: " $objItem.Model

write-host }

-Dan B

skrilla99 SSCertifiable Points: 5795 More actions · Answer 9

You could also run systeminfo remotely from your local command line:

c:\>systeminfo /S serverName | find "System Model:"

Which could be readily put together in a batch for all servers.

I don't see a clean way to get the servername to output on the same line though...

-Dan

Lowell SSC Guru Points: 323524 More actions · Answer 10

skrilla99 (8/23/2011)
You could also run systeminfo remotely from your local command line:
c:\>systeminfo /S serverName | find "System Model:"
Which could be readily put together in a batch for all servers.
I don't see a clean way to get the servername to output on the same line though...
-Dan

Dan I've done it like this with an update after i've done som xp_cmdShell work:

assume the logic below was in a loop for each server.....

create table #Results (

ID int identity(1,1) NOT NULL,

ServerName varchar(255),

TheOutput varchar(1000))

--the loopy logic:

insert into #Results (TheOutput)

exec master..xp_cmdshell 'systeminfo /S DEV223 | find "System Model:"'

UPDATE #Results SET ServerName = 'DEV223' WHERE SERVERNAME IS NULL

select * from #Results

drop table #Results

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 11

Allen white made a PowerShell script that may help you. Check his article[/url]. I hope it helps.

My blog

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 12

skrilla99 (8/23/2011)
Express12 (8/23/2011)
We have over 150 instances of SQL Server installed on servers. I'd like to identify those instances installed on a VM and those installed on physical machines.. Spiceworks looks interesting. (spiceworks.com)
What local server rights do you have? Is xp_cmdshell enabled?
I'm sure there is a better way, but you could try something like:
exec xp_cmdshell 'systeminfo | find "System Model:"'
Even better if you can run this against a central management server to do them all at once.
-Dan B

I will not recommend you to enable xp_cmdshell without first reading this.

My blog

skrilla99 SSCertifiable Points: 5795 More actions · Answer 13

Ignacio A. Salom Rangel (8/23/2011)
skrilla99 (8/23/2011)
Express12 (8/23/2011)
We have over 150 instances of SQL Server installed on servers. I'd like to identify those instances installed on a VM and those installed on physical machines.. Spiceworks looks interesting. (spiceworks.com)
What local server rights do you have? Is xp_cmdshell enabled?
I'm sure there is a better way, but you could try something like:
exec xp_cmdshell 'systeminfo | find "System Model:"'
Even better if you can run this against a central management server to do them all at once.
-Dan B
I will not recommend you to enable xp_cmdshell without first reading this.

Agreed.

Perry Whittle SSC Guru Points: 234040 More actions · Answer 14

This will work

exec sp_configure 'show advanced options', '1'

reconfigure with override

exec sp_configure 'xp_cmdshell', '1'

reconfigure with override

set nocount on

create table #services(stext varchar(128))

insert into #services

exec xp_cmdshell "net start"

If (select * from #services where stext like '%vmware%tools%') is not null

begin

Print '!!This is a virtual machine!!'

end

drop table #services

exec sp_configure 'xp_cmdshell', '0'

reconfigure with override

exec sp_configure 'show advanced options', '0'

reconfigure with override

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"

Determine if SQL Server is running on VM

Cookies on SQLServerCentral