24 January,2015 by Jack Vamvas
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:
b)SQL Server Inventory management
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