Determine if SQL Server is running on VM

  • Express12

    SSCoach

    Points: 18447

    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
  • Perry Whittle

    SSC Guru

    Points: 233824

    which hyper visor are you using?

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

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Express12

    SSCoach

    Points: 18447

    VMware

    BT
  • GregoryF

    SSCertifiable

    Points: 6296

    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: 18447

    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

    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

    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

    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

    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

    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: 323444

    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

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

  • Ignacio A. Salom Rangel

    SSC-Insane

    Points: 20443

    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.

  • skrilla99

    SSCertifiable

    Points: 5795

    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: 233824

    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

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

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

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

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