Physical or Virtual

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.


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.

[codesyntax lang=”powershell”]


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.

[codesyntax lang=”tsql”]


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.

[codesyntax lang=”tsql”]


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.


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).

[codesyntax lang=”tsql”]


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.

2 thoughts on “Physical or Virtual”

  1. For your TSQL script, the virtual_machine_type column is only available if you are running 2008R2 with SP1 or greater. It’s not available on the RTM version or with any of the RTM Cumulative Updates.

Leave a Reply to Jason Brimhall Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.