Query VMWare vCenter VCDB database for guest and host details

If you’re  managing SQL Servers hosted on VMWare  ESX hosts , getting  host and guest details from the VMWare VCDB database is useful.  Normally a DBA will request the information from a VM administrator or access the vCenter GUI interface.   Information displayed on vCenter  is stored in a SQL Server database called VCDB.

 The VCDB database stores details about the underlying virtualization details. In a previous post  Virtualization – and database servers I’ve discussed the need to  review memory, storage and CPU details of the virtual infrastructure.

It is possible to query the VCDB database directly and get the information required from the underlying tables and views.

I’ve queried the VCDB database for different purposes . Some examples:

a)performance troubleshooting

b)SQL Server Inventory management

c)configuration details

Query to get guest and host details from VMWare vCenter VCDB database

This query returns all guests with SQL in the name across all ESX clusters

SELECT 
VMS.name AS [Server Name], VCR.name AS [Cluster Name], HOS.name as [Host Name], 
VMS.DNS_name, VMS.IP_ADDRESS, HOS.hostid, *
 FROM vpxv_vms VMS 
 INNER JOIN VPXV_HOSTS HOS on VMS.hostid = HOS.hostid
 INNER JOIN VPXV_COMPUTE_RESOURCE VCR on HOS.farmid = VCR.RESOURCEPOOLID
   WHERE VMS.name like '%SQL%' 
 ORDER BY VCR.name, VMS.name

Read More on Virtualization

How to find VMWare ESX Host event messages using VPXV_EVENTS

SQL Server – virtualization - is it a viable alternative

Database Virtual Server Candidacy Criteria