How to find out SQL server is installed on a VM or Physical machine
Question
Tuesday, January 10, 2017 9:39 AM
I am new DBA and collecting inventory information of all my SQL servers. I need to find out if SQL server is installed on a physical machine or virtual machine. Please help.
All replies (15)
Tuesday, January 10, 2017 9:51 AM ✅Answered
Hi,
You can use below TSQL to find out this information. You need to execute this on SQL serer for which you want to find out information.
sp_readerrorlog 1,1, 'System Model'
This will display information like this:
For VM Output will be like. You may see manufacturer name diff.
System Manufacturer: 'Microsoft Corporation', System Model: 'Virtual Machine'.
For Physical Machine Output will be like:
System Manufacturer: 'Dell Inc.', System Model: 'Latitude E7250'.
Kindly mark the reply as answer if they help
Tuesday, January 10, 2017 10:22 AM ✅Answered
You can use query to WMI repository if you want to do this pragmatically. You can use WMI query for both SQL and non SQL servers.
Another method I uses is going to machine and
Run>msinfo32 and press enter. This will display system information. Under System model you will find out whether VM or physical machine.
Thanks,
Kindly mark the reply as answer if they help
Tuesday, January 10, 2017 10:41 AM ✅Answered
I need to find out if SQL server is installed on a physical machine or virtual machine. Please help.
There is a DMV which tells you underlying OS is virtual or physical. The DMV was introduced in SQL Server 2008
select virtual_machine_type_desc from sys.dm_os_sys_info
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Tuesday, January 10, 2017 9:52 AM
Hello,
SQL Server is not Aware about the underlying system; that's just a black box. You have to fetch this information somehow on OS level instead.
Olaf Helper
Tuesday, January 10, 2017 10:04 AM
Roughly how many instances do you have? In a large enterprise, you can get this from SCCM. For a smaller list, you can use PowerShell to get the server OS information or query the WMI classes. You can also run TSQL against each of the servers if the list is small. Regardless of the path you select, you're collecting the underlying OS properties as opposed to something within SQL Server, since it just sits on top of the OS.
Thanks,
Sam Lester (MSFT)
https://blogs.msdn.microsoft.com/samlester/
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.
Tuesday, January 10, 2017 10:13 AM
Thank you. this work for sql servers, I have non sql servers like SSIS and SSRS server. Please provide if there is any method of finding this information on those server also. Appreciate your help.
Tuesday, January 10, 2017 10:16 AM
It's the same, collecting OS information (WMI, PS or any other way) is not depending whether it's SQL server or non-sql.
Tuesday, January 10, 2017 10:17 AM
we have almost 50+ SQL servers and 10 to 15 box where SSIS or SSRS is installed. My task to maintain a consistent repository which has all information of servers. I am working with a developer to develop a UI which connect to SQL server and pull details.
Tuesday, January 10, 2017 10:28 AM | 1 vote
Or my way to find:
Get list of servers into txt
Powershell:
get content of txt into variable, run a loop with following command on each entry:
$Servers = Get-Content yourfile.txtforeach($Server in $Servers){Get-NetAdapter -CimSession $Server | select InterfaceDescription}
it will either return you Hyper-V or vmxnet depending on which virtualization platform you use. If none of this is returned then it's physical one
Tuesday, January 10, 2017 10:34 AM
Thank you all for information. I will check and verify method which will be good for me.
Tuesday, January 10, 2017 10:38 AM
Hi there, the simplest way is just open the latest ERRORLOG file on the MSSQL folder.
On System Manufacturer you'll see either physical or virtual:
Tuesday, January 10, 2017 10:45 AM
Thank you Shanky_621. This is useful.
Wednesday, January 11, 2017 4:44 AM
Hi VSSQL,
>>I need to find out if SQL server is installed on a physical machine or virtual machine.
Instead of query against every single instance, I would say it’s easier to use Microsoft Assessment and Planning Toolkit to generate a report regarding all SQL Server instances in your environment. For more information, please download MAP_Sample_Documents from here and review SqlServerAssessment sample report.
If you have any other questions, please let me know.
Regards,
Lin
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Wednesday, January 11, 2017 5:00 AM
Thank you Lin. we are developing and more than half developed our own small tool which will connect to provided sql servers and periodically maintain repository. we are planning to use same tool later for monitoring as well.
Wednesday, January 11, 2017 1:21 PM
Hi VSSQL,
you can use following command.
DECLARE @result int
EXEC @result = xp_cmdshell 'SYSTEMINFO'
-- see system manufacture and system modelexec xp_cmdshell 'systeminfo | find "System Model:"'
exec xp_cmdshell 'systeminfo | find "System Manufacturer:"'
refer Fig for Physical.
Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.