SQL RNNR

Today I get to share something that has been on my someday list for quite some time.  I have planned on getting a solution that involves TSQL to help determine if you are running SQL Server on a physical machine or if it happens to be a virtual machine.  Now, I am prepared to share two means that involve using TSQL to achieve that goal.

I get to cover the spectrum with these two solutions.  At one end, we have something that is relatively simple.  It only works in SQL 2008 R2 and above though.  At the other end of the spectrum, we get to use something that involves more of a sledgehammer.

Covering these two extremes is useful.  As I said, the simple solution at the simple end of the spectrum is not going to work if you are running SQL Server 2008 or older.  So, if you are running SQL 2005, for example, you would need something a bit less delicate.  I will leave it to you to determine if it is worth it to use the “sledgehammer” approach.

Sledgehammer

I’m calling this the sledgehammer approach because it is not a 100% TSQL solution.  Some may not like the idea due to the use of xp_cmdshell – making the sledgehammer more like a bull in a china shop.

Up front, this solution utilizes tools that are readily available.  Those tools are PoSH, WMI and TSQL.

Let’s first look at the WMI.  I knew I could find the information I wanted if I could query WMI.  All that was needed was a means to get to the Win32_ComputerSystem class.  From there I could get the information for manufacturer and model for the machine.  Virtual machines tend to have a manufacturer such as the the following “VMWare”, “innotek”, and “Microsoft Corporation” – to list some of the more popular options.

Next, we can get to the WMI very easily via PoSH or vbscript.  I was having a devil of a time trying to figure out a sane method of doing it via TSQL only.  That is fine, because I was able to utilize PoSH quite nicely in this case.  After, having found a reference for what I wanted to do from here, I was able to create the script that I needed.  Here is that script.

Code block

Param ([string]$HostName="localhost",$Option=1)
 
function ExecQry {
    param ([string]$CmdStr)
         $cmdStr
    }
 
if ($Option=1){
    $x = ExecQry "gwmi Win32_ComputerSystem -computer `$hostname" | iex
    $x | fl manufacturer,model
 
}

Nothing too terribly fancy there.  I am only querying for the two attributes that I really want to accomplish my goal.  Those attributes being: manufacturer and model.

Next comes the difficult part.  Running all of this from within TSQL and capturing useful results.  If you execute that PoSH script, you will notice that the presentation of the results is really lacking.  First though I needed to get the script to execute from within SSMS.

I started out miserably with getting that to run.  The script just kept hanging and would never even cancel out.  This is what I started out with in trying to get it to run.

Code block

xp_cmdshell 'powershell -noexit "& ""C:\Admin\PoSH Scripts\GetMachineInfo2.ps1"""'

If I extracted the powershell command and ran it from a command prompt, it would run flawlessly.  From within SSMS – crash and burn every time.  Then, I decided to try removing the -noexit and see if I could get different results.  Voila – fantastic results.  I now had the basis for getting this running in SSMS.

In addition to the use of xp_cmdshell, I felt it prudent to use a string splitter to help tidy up the presentation.  The string splitter I like to use (delimited split function) can be found here.  I also felt it necessary to use Pivot – again to help tidy up the results in the presentation.

I know, you’re itching to see the script now, so here it is.

Code block

DECLARE @processing TABLE (ID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),Results VARCHAR(256))
DECLARE @PartTwo TABLE (ID INT, subID INT, Somevalue NVARCHAR(100))
 
DECLARE @PathtoPS1			VARCHAR(256)
		,@CommandtoExec		VARCHAR(512)
 
SET @PathtoPS1 = 'your file system path\GetMachineInfo2.ps1'
SET @CommandtoExec = 'powershell "& ""' + @PathtoPS1 +'"""'
;
 
INSERT INTO @processing(Results)
	EXEC xp_cmdshell @CommandtoExec
 
;
 
INSERT INTO @PartTwo (ID,subID,Somevalue)
	SELECT pr.ID,ss.ItemNumber,LTRIM(RTRIM(ss.Item)) AS Somevalue
		FROM @processing pr
		CROSS APPLY AdminDB_Test.dbo.stringsplitter(pr.Results,':') ss
 
;
SELECT Manufacturer,Model
		,CASE
			WHEN Manufacturer like '%Vmware%'
				THEN 'ISVIRTUAL'
			WHEN Manufacturer like '%innotek GmbH%'
				THEN 'ISVIRTUAL'
			WHEN Manufacturer like '%Microsoft Corporation%' AND Model like '%Virtual%'
				THEN 'ISVIRTUAL'
			ELSE 'PhysicalMachine'
			END AS VirtualorPhysical
	FROM (SELECT pt.SomeValue AS Property,p2.somevalue AS PropertyVal
			FROM @PartTwo pt
				INNER Join @PartTwo p2
					ON pt.id = p2.id
					And pt.subid <> p2.subid
			WHERE pt.somevalue in ('manufacturer','model')
			) B
	PIVOT (MIN(PropertyVal) FOR Property IN (manufacturer,model)) AS P

Unless you have xp_cmdshell disabled, the only change you will need to make is for the @PathtoPS1 variable.  Save the PoSH script on your file system with the name GetMachineInfo2.ps1, and you will be all set.

Elegance

Now that you have seen the hard way of doing it, here is what we can do in SQL Server 2008 R2 (must have SP1 applied at a minimum – thanks to Nic Cain for that info).

Code block

SELECT @@SERVERNAME AS SRVName,virtual_machine_type
	FROM sys.dm_os_sys_info

The virtual_machine_type attribute is a new addition to this DMV as of SQL 2008 R2.  There are three possible values: 0,1, or 2.  The value of 0 means that the machine is physical.  Any other value means that it is a virtual machine.  You can read more about that from MSDN.

There you have it.  Two methods within SSMS that you can extrapolate where a Server is physical or virtual.