Gather information from VMware vCenter VCDB about SQL Server


By:   |   Updated: 2014-04-23   |   Comments (17)   |   Related: More > Virtualization

Problem

We have over a hundred SQL Server instances with versions from SQL Server 2000 to SQL Server 2012 some of which are physical servers, but the majority being VMware virtual servers. While we have some access to the vSphere Client Console and the vSphere Web Client there is still more information that we cannot see or that we cannot find easily from these tools. It may be for some organizations that the SQL Server DBAs will not have access to the VMware tools or have lots of hoops to jump through to get the access or information. Fortunately, as the DBA you may have access to the SQL Server instance storing the VMware vCenter Server Database.

We can if we exercise extreme care and only read from the views and tables in the vCenter database, we can gather lots of information on the virtual servers (VM Guests) in our VMware environment. This would include VM Guest disk space (VMDK Files aka Virtual Disks), the Datastores (SAN, ISCSI, NFS, DAS) the VM Guests VMDK files are located on, whether the VM Guests are on thin provisioned disks and other general configuration of the VM Guests themselves. Further information can be gathered including VM Guest performance data, ESXi host configuration information and performance data on the ESXi hosts themselves. If we do use the performance data then we should be aware that it is granular, by default, of 5 minutes with rollups of 30 minutes for weekly, 2 hours for monthly and daily for the year. These are adjustable in vSphere vCenter and performance monitoring has to be enabled as well to gather the performance data (multiple levels of detail can be configured).

Finally it should be stated that this method of finding information from the vSphere vCenter database, called VCDB unless changed, is not officially supported by VMware and if you are not careful and use a read-only connection can result in corruption of the vCenter database and failure of the VMware farm and/or features. However, because we will exercise the utmost caution we can uncover a lot of useful information on the VMware virtual SQL servers we manage.

Creating T-SQL queries from the VMware database views and tables

Configuration Information About the Guest VMs

The first T-SQL query we will look at is one against the VPXV_VMS view which has configuration information about the guest VM. There is much you can learn about the guest VM from this view, Memory size, vCPUs, OS version, DNS name and IP, number of NICs, number of Disks and VMware tools information. If you are not familiar with what the data represents then a little research online will make it clear.

Be sure to use WITH (NOLOCK,NOWAIT) when querying the database tables and views.

USE VCDB;
GO
SELECT
   [VMID]
 , [NAME]
 , [VMGROUPID] -- key into dbo.VPXV_VMGROUPS
 , [HOSTID] -- ESXi host key into dbo.VPXV_HOSTS
 , [CONFIGFILENAME]
 , [VMUNIQUEID]
 , [RESOURCE_GROUP_ID] -- key into dbo.VPXV_RESOURCE_POOL
 , [MEM_SIZE_MB]
 , [NUM_VCPU]
 , DATEADD(HOUR,-6,[BOOT_TIME]) AS BootTime -- need to adjust for time zone/daylight savings
 --, [SUSPEND_TIME]
 , [POWER_STATE]
 --, [Guest_OS]  AS origGuest_OS
 , CASE [Guest_OS]
  WHEN 'centosGuest' THEN 'CENTOS'
  WHEN 'other26xLinux64Guest' THEN 'Linux 2.6 Kernel 64 bit'
  WHEN 'other26xLinuxGuest' THEN 'Linux 2.6 Kernel 32 bit'
  WHEN 'otherGuest' THEN 'Unknown'
  WHEN 'redhatGuest' THEN 'Red Hat 32 bit'
  WHEN 'rhel4_64Guest' THEN 'Red Hat 4 64 bit'
  WHEN 'rhel5Guest' THEN 'Red Hat 5 32 bit'
  WHEN 'sles11_64Guest' THEN 'SLES 11 64 bit'
  WHEN 'win2000ServGuest' THEN 'Windows 2000 Standard'
  WHEN 'windows7Guest' THEN 'Windows 7 32 bit'
  WHEN 'windows8Server64Guest' THEN 'Windows 2012'
  WHEN 'windows7Server64Guest' THEN 'Windows 2008 R2'
  WHEN 'winLonghorn64Guest' THEN 'Windows 2008 64 bit'
  WHEN 'winLonghornGuest' THEN 'Windows 2008 32 bit'
  WHEN 'winNetEnterpriseGuest' THEN 'Windows 2003 Enterprise 32 bit'
  WHEN 'winNetStandard64Guest' THEN 'Windows 2003 Standard 64 bit'
  WHEN 'winNetStandardGuest' THEN 'Windows 2003 Standard 32 bit'
  WHEN 'winVistaGuest' THEN 'Windows Vista 32 bit'
  WHEN 'winXPProGuest' THEN 'Windows XP Pro 32 bit'
  WHEN 'winNetEnterprise64Guest' THEN 'Windows 2003 Enterprise 64 bit'
  ELSE 'UnSpecified'
  END AS GuestOS
 --, [GUEST_FAMILY]
 , [GUEST_STATE]
 , ROUND(([MEMORY_RESERVATION]/(1024*1024)),0) AS Mem_Resv
 , ([MEMORY_OVERHEAD]/(1024*1024)) AS Mem_Ovhd
 , [CPU_RESERVATION]
 , [DNS_NAME]
, [IP_ADDRESS]
 , [VMMWARE_TOOL]
 , [TOOLS_VERSION]
 , [NUM_NIC]
 , [NUM_DISK]
 , CASE [IS_TEMPLATE] WHEN 1 THEN 'True' WHEN 0 THEN 'False' End AS Template
 , [DESCRIPTION]
 , [ANNOTATION]
 --, [SUSPEND_INTERVAL]
 , CONVERT(DECIMAL(10,0),ROUND(([AGGR_COMMITED_STORAGE_SPACE]/(1024*1024)),0)) AS Agg_CommDiskMB 
 , CONVERT(DECIMAL(10,0),ROUND(([AGGR_UNCOMMITED_STORAGE_SPACE]/(1024*1024)),0)) AS Agg_UnCommDiskMB
 , CONVERT(DECIMAL(10,0),ROUND(([AGGR_UNSHARED_STORAGE_SPACE]/(1024*1024)),0)) AS Agg_UnSharDiskMB
 , DATEADD(HOUR,-6,[STORAGE_SPACE_UPDATED_TIME]) AS StorUpdTime -- adjust for time zone/daylight savings
FROM
 [VCDB].[dbo].[VPXV_VMS] WITH (NOLOCK,NOWAIT)
ORDER BY
 [NAME];

The data returned by this query is voluminous and may not make much sense to you if you have not worked with VMware from the infrastructure side of things, but do not be discouraged. There is still quite a lot that can be used to help you keep track of configuration and performance information to troubleshoot and verify your SQL Server Guest VMs.

Verify your SQL Server Guest VMs

In the image above we see that we have the Name inside vCenter (which may not be the actual DNS server name, more of a logical name), VMGroupID relates to folder hierarchy/view in vSphere client, HostID is the ESXi host table key, Configfile is the name of the VMware Guest configuration file, VMUniqueID is a unique GUID assigned to the VM Guest.

We have the Name inside vCenter

The second image shows the configured Memory, number of virtual CPUs, VM Guest boot time, powered on state, the VM Guest OS and Guest OS state. This is some very good information for troubleshooting and performance checking, you can see what virtual hardware configurations are on each SQL Server without logging in or looking at some monitoring tool.

You can see what virtual hardware configurations are on each SQL server

The third image above shows the IP address for the VM Guest which could be helpful if you have multiple NICs, which could be a "random" pick of the IP addresses, so for cluster it could be the heartbeat NIC. In general it will be correct with what is in DNS. I did not show the DNS_NAME column, but it will contain the FQDN of the VM Guest which is the actual name as seen in AD or DNS, and as I mentioned after the first image, may not be the same as the logical name in the VCenter DB. There is information on the state and version of VMTools running in the VM Guest, no VMTools running and some of this data would not exist. We have the number of Virtual NICs configured for the VM Guest and the number of Disks reported from within the OS, not necessarily equal to the VMware VMDK disk files making up the VM Guest. Finally we see if the VM Guest is a template that can be utilized to create new VM Guest machines.

The VM Guest is a template that can be utilized to create new VM Guest machines

The fourth and last image above shows the VMware Disk storage space for the VM Guest. A rough answer as to this disk size information would to be take the Agg_CommDiskMB as the amount of space used by all VMware disks (not OS disks) on all Datastores, the Agg_UnCommDiskMB as the unused space left that can be allocated to the total provisioned space by all VMware disks for the VM Guest. That leaves Agg_UnSharDiskMB which is the amount of disk space associated exclusively with the Guest VM by all VMware disks (VMDK, SWAP, SNAP, etc.).

As an example if you have a VM Guest with two drives (VMware VMDK disks not OS disks, could be more than two OS disk drives carved out of the VMware VMDK files) of 80GB each then it may report Agg_CommDiskMB: 28234, Agg_UnCommDiskMB: 140225 and Agg_UnSharDiskMB: 28234 for the values. This means that ~ 27GB (28234/1024) is committed on all VMDK files on all Datastores, ~ 137GB is not committed out of a total ~ 160GB provisioned (we are ignoring SWAP, Delta (snapshot) disk for now) with ~ 27GB (28234/1024) unshared by any other Guest (think clusters to share disks). Again these are VMware files (VMDK, SWAP, SNAP, etc.) and not the disks as seen inside the Guest's OS.

It can take a little while to understand this, but realize that many things affect the numbers returned by these queries including thin provisioning and shared disks. So if the numbers seem different then there may be snapshots, attached but unused VMDK files or other reasons, just do not panic. You can get with the VMware and Storage admins to learn some more about Vmware and storage.

Query for the Size of the VM Guest Disks

The second query shown below is against a table that contains information on the size of the VM Guest Disks as reported by the OS and as such requires that the VMTools be installed on the Guest VM and be functional. Therefore, it may not have all disk information, but in most cases it will be accurate if the VMTools are running.

USE VCDB;
GO
SELECT -- vgd.[VM_ID]
   REPLACE((UPPER(vv.[DNS_NAME])),'.mydomain.com','') AS DNS_NAME
 , vv.[IP_ADDRESS]
 , vgd.[PATH]
 , ((CONVERT(BIGINT,vgd.[CAPACITY]))/(1024*1024*1024)) AS CapacityGB
 , ((CONVERT(BIGINT,vgd.[FREE_SPACE]))/(1024*1024*1024)) AS FreeGB
 , CONVERT(DECIMAL(5,1),(CONVERT(DECIMAL(16,0),vgd.[FREE_SPACE]))/(CONVERT(DECIMAL(16,0),vgd.[CAPACITY]))*100) AS Pct_Free
FROM
 [VCDB].[dbo].[VPX_GUEST_DISK] AS vgd WITH (NOLOCK,NOWAIT)
  INNER JOIN [VCDB].[dbo].[VPX_VM] AS VV WITH (NOLOCK,NOWAIT)
   ON VV.[ID]=vgd.[VM_ID]
ORDER BY
   vv.[DNS_NAME]
 , vgd.[PATH];

The results give the actual AD name

The results give the actual AD name, IP address (again could be heartbeat, etc.), the Path as reported by OS, total disk capacity from the OS perspective, free disk space from an OS perspective and the percentage free. This is a nice and easy way to find disks and to display configuration information from inside the OS.

Physical Disk Information for Guest VMs

The next T-SQL query is useful to show the physical location layout of the VMDK files that makeup the VMware files for the Guest VM. We can use this to make sure that the data, log and Tempdb files that make up the structure of a database are on separate physical Datastores (LUNS/Storage). Each row represents a separate physical location where VMDK, SNAP or other files are stored for each VM Guest.

USE VCDB;
GO
SELECT   vv.[NAME] AS VM_Name
-- , vvd.[VM_ID]
-- , vvd.[DS_ID]
 , vd.[NAME] AS DataStore_Name
 --, vd.[STORAGE_URL]
 --, vd.[CAPACITY]
 , ((CONVERT(BIGINT,vd.[CAPACITY]))/(1024*1024*1024)) AS CapacityGB
 --, vd.[FREE_SPACE]
 , ((CONVERT(BIGINT,vd.[FREE_SPACE]))/(1024*1024*1024)) AS FreeGB
FROM
 [VCDB].[dbo].[VPXV_VM_DATASTORE] AS vvd WITH (NOLOCK,NOWAIT)
  INNER JOIN
   [VCDB].[dbo].[VPXV_DATASTORE] AS vd WITH (NOLOCK,NOWAIT)
    ON
     vd.[ID] = vvd.DS_ID
  INNER JOIN [VCDB].[dbo].[VPXV_VMS] AS vv WITH (NOLOCK,NOWAIT)
   ON
    vv.VMID = vvd.[VM_ID]
ORDER BY
 VM_Name;

The next TSQL query is useful to show the physical location layout of the VMDK files that makeup the VMware files for the guest VM

The T-SQL query results above give the Datastore information for the Guest VMs. It tells you where the VMDK files (VMware not OS) that make up the Guest VM are located physically such as a SAN LUN(s) or ISCSI LUN(s), NFS volume, etc. The Datastore Name is a logical name in VMware, but it does represent the actual physical storage connected to the ESXi hosts. The Datastores Total Capacity and Free space are also given. Be aware that if thin provisioning of the SAN and/or VMWare is being used then you can have a false sense of security or you can panic, neither is good. I will talk about thin provisioning after the last query.

Disk Properties in VMWare for Virtual Hard Disks

This last VMware T-SQL query is one that shows whether the VMDK files that make up the virtual hard disks are thin or thick provisioned, persistent or independent disks and whether the disk is a Raw Disk Mapped (RDM) disk. Why do you care about these pieces information? They affect the performance, ability to snapshot the disk, HA characteristics of the VM Guest for DR/HA and vMotioning the guest.

USE [VCDB];
GO
SELECT
   VM.NAME
 , REPLACE((UPPER(VM.[DNS_NAME])),'.MyDomain.LDAP','') AS DNS_NAME
 , VM.POWER_STATE AS POWER_ST
 , VM.GUEST_STATE AS RUNNING_ST
 , FBX.[DEVICE_BACKING_DISK_MODE] AS DISK_MODE
 , CASE WHEN FBX.EAGERLY_SCRUB = 1 THEN 'TRUE' ELSE 'FALSE' END AS EAGERLY_SCRUB
 , CASE WHEN FBX.DEV_BAC_THIN_PROVISIONED_FLG = 1 THEN 'TRUE' ELSE 'FALSE' END AS THIN_PROVISIONED
 , CASE WHEN FBX.DEV_BAC_COMPAT_MODE = 'physicalMode' THEN 'TRUE' ELSE '' END AS RDM
 , VVD.DEVICE_INFO_LABEL AS DISK_LABEL
 --, VVD.DEVICE_INFO_SUMMARY  -- has issues with reporting 0 bytes when size is not 0
 , CAST(VD.HARDWARE_DEVICE_CAPACITY_IN AS BIGINT)/(1024*1024) AS DiskSizeGB
 , DS.NAME AS DATASTORE
 , FB.HARD_DEVICE_BACKING_FILE_NAME AS VMDK_FILE
FROM
 [dbo].[VPX_VDEVICE_FILE_BACKING_X] AS FBX WITH (NOLOCK,NOWAIT)
  INNER JOIN [VCDB].[dbo].[VPX_VDEVICE_FILE_BACKING] AS FB WITH (NOLOCK,NOWAIT)
   ON FB.BACKING_ID = FBX.BACKING_ID
  INNER JOIN [VCDB].[dbo].[VPXV_VMS] AS VM WITH (NOLOCK,NOWAIT)
   ON VM.VMID = FBX.VM_ID
  INNER JOIN [VCDB].[dbo].[VPXV_DATASTORE] AS DS WITH (NOLOCK,NOWAIT)
   ON DS.ID = FB.DATASTORE_ID
  INNER JOIN [VCDB].[dbo].[VPX_VIRTUAL_DISK] AS VD WITH (NOLOCK,NOWAIT)
   ON FB.VM_ID = VD.VM_ID
    AND FB.UPDATE_KEY = VD.UPDATE_KEY
  INNER JOIN [VCDB].[dbo].[VPX_VIRTUAL_DEVICE] AS VVD WITH (NOLOCK,NOWAIT)
   ON VVD.VDEVICE_ID = VD.VDEVICE_ID
WHERE
 REPLACE((UPPER(VM.[DNS_NAME])),'.MyDomain.LDAP','') IN    
(
  'MySQLSrv01'
 ,'MySQLSrv02'
 --
 -- List of SQL Server VM Guests
 --
 ,'MySQLSrv99'
 ,'MySQLSrv100'
)   
ORDER BY
 VM.NAME;

In the result set of the TSQL query you see the vCenter Name, AD name (FQDN stripped)

Next are the Disk Mode, Persistence, Eager Scrubbing, Thin/Thick provisioned and lastly if it is an RDM disk

In the result set of the T-SQL query you see the vCenter Name, AD name (FQDN stripped), the Power state and the Running state, whether it is powered on inside VMware and also actually running. Next are the Disk Mode, Persistence, Eager Scrubbing, Thin/Thick provisioned and lastly if it is an RDM disk. If you are wondering about the difference between Power state and Running state, if you are powered on then it is the same as with physical system, if you are running then you are processing in the OS, but you could also be suspended which is where the machine state is frozen. Eager scrubbing is zeroing out the disk so that there is protection against latent disk errors. The second part shows the disk label from VMware Guest configuration (inside vSphere), the provisioned VMware disk size and which Datastore the VMDK file is located on. The actual name of the VMDK file is in the last column; this is nice to know from a configuration viewpoint. These results tell us quite a lot about our virtual disks and how they will perform in SQL Server.

I am going to talk briefly about thin/thick provisioned, independent/persistent and whether it is an RDM disk. These have great amount to do with I/O (disk) performance and how the VM Guest is handled with regard to HA/DR options. The thick and thin provisioning refers to whether the entire VMware virtual disk space is allocated (thick) or allocated as needed (thin). For SQL Server it is recommended that the data, log and Tempdb disks be thick provisioned for production servers as well as each be in a separate virtual disk and on a separate datastore. That prevents them from having to grow suddenly which would be seen as pause and reduces contention for I/O by having "separate spindles" for each disk. In terms of Persistent and Independent, if the disk is persistent the changes are written to disk immediately, not persistent the changes are discarded if you power off VM Guest. Independent disks are excluded from snapshots which may affect vMotion capabilities. RDMs, Raw Disk Mapped, are physical disks that are not VMDK files, but SAN LUNs (in general) that are attached to the VM Guest and the data is passed through the VM to the SAN LUN, which can affect snapshotting, cloning and vMotioning. So this is important information to have on your SQL Servers and you really need to know how your VMware disks are configured.

Some further information (Hyper-V Guest query)

Some next steps we could take are to pump this information into a SQL configuration database using T-SQL or combine with PowerShell scripting. We can also look at the performance data and see if we can use it for troubleshooting or reporting. We have to be careful in how we interpret the data though; we should probably become better friends with our VMware and SAN admins and ask them for help.

A final bonus T-SQL query which I would like to show is one that finds Hyper-V guest VMs in your environment using Active Directory. It will require that you setup the ADSI linked server provider in SSMS, but that has been covered in prior tip Querying Active Directory Data from SQL Server. The Hyper-V guests have an extra LDAP node (AD Object) created in them that has attributes which define it as a Hyper-V VM Guest in the AD Domain if the VM Guest is a member of the AD Domain.

SELECT    LEFT(RIGHT(distinguishedName,LEN(distinguishedName)-30),CHARINDEX(',',RIGHT(distinguishedName,LEN(distinguishedName)-30)) -1) AS SrvName
 , RIGHT(RIGHT(distinguishedName,LEN(distinguishedName)-30),(LEN(RIGHT(distinguishedName,LEN(distinguishedName)-30))
  - CHARINDEX(',',RIGHT(distinguishedName,LEN(distinguishedName)-30)))) AS DN_Location
FROM OpenQuery(ADSI, 'SELECT
    cn
    , distinguishedName
    , objectGUID
   FROM
    ''LDAP://dc=mydomain,dc=ldap''
   WHERE
    CN=''Windows Virtual Machine'' '
    )
ORDER BY
 SrvName;

In the image of the results below, we get the AD Server name and the OU location in AD of the server object. There are additional attributes in the AD object, but if you need them you can extract them with the same type of query. Use Softerra's LDAP Browser 4.5, free; to take a look at AD with LDAP browser and find the attributes you want.

Use Softerra's LDAP Browser 4.5, free; to take a look at AD with LDAP browser and find the attributes you want

That completes the initial look into VMware using the vCenter DB. There is a lot more information and configuration data to be extracted from the database. Just be sure to exercise caution, read-only with NOLOCK and NOWAIT since the DB is live and in use, and never write to the DB under any circumstances.

In the links below there is a document from VMware describing the Views found in the vCenter DB, which you should use to help identify keys and useful information. There are in addition several other links with much more information on VMware Disks.

Next Steps


Last Updated: 2014-04-23


get scripts

next tip button



About the author
MSSQLTips author Brian P ODwyer Brian P ODwyer is a BI Analyst at Cook Children's Health Care System who knows both Infrastructure and SQL server

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, November 28, 2016 - 12:06:15 PM - Brian ODwyer Back To Top

Here is code I used to find the SCSI controller type and NIC type for a VM guest. You should be able to modify the code in the article to use this to add the controller info.

 

SELECT
 --  [ID]
 --, [VDEVICE_ID]
  VPG.[NAME] AS VMGuestLogicalName
 , VPH.[NAME] AS ESXIHostName
 , ISNULL(VPG.[DNS_NAME],'') AS DNS_NAME
 , ISNULL(VPG.[IP_ADDRESS],'') AS IP_ADDRESS
 , VCR.NAME AS ClusterName
 --, [CONNE_AL_GU_CONTROL_FLG]
 --, [CONNECTABLE_CONNECTED_FLG]
 --, [CONNE_START_CONNECTED_FLG]
 , [CONNECTION_STATUS]
 , REPLACE([VPX_TYPE],'vim.vm.device.','') AS [VPX_TYPE]
 , [DEVICE_INFO_LABEL]
 , [DEVICE_INFO_SUMMARY]
 --, [DEVICE_CONTROLLER_KEY]
 --, [DEVICE_UNIT_NUMBER]
 --, [DEVICE_KEY]
 --, [DEVICE_METHOD_INFO_KEY]
 --, [DEVICE_TYPE_INFO_KEY]
 --, [DEVICE_ELEMENT_INFO_KEY]
 --, [HARD_DEV_DEVICE_INFO_FREQUENCY]

FROM
 [dbo].[VPX_VIRTUAL_DEVICE] AS VVD WITH (NOLOCK,NOWAIT)
  LEFT OUTER JOIN [dbo].[VPXV_VMS] AS VPG WITH (NOLOCK,NOWAIT)
   ON VPG.VMID = VVD.ID
  INNER JOIN [dbo].[VPXV_HOSTS] AS VPH WITH (NOLOCK,NOWAIT)
   ON
    VPH.HOSTID = VPG.HOSTID
  LEFT OUTER  JOIN  [dbo].[VPXV_RESOURCE_POOL] AS VRP WITH (NOLOCK,NOWAIT)
   ON
    VRP.RESOURCEPOOLID = VPG.RESOURCE_GROUP_ID
  LEFT OUTER JOIN  [dbo].[VPXV_COMPUTE_RESOURCE] AS VCR  WITH (NOLOCK,NOWAIT)
   ON
    VCR.RESOURCEPOOLID = VRP.PARENT_ID
WHERE
 --[DEVICE_INFO_LABEL] LIKE 'Network adapter%'
 [DEVICE_INFO_LABEL] LIKE 'SCSI controller%'

 

 

This code should get ESXi controller info

SELECT
   VPH.[NAME]AS [NAME]
 , VHB.[STATUS_VAL] AS [STATUS]
 --, VHB.[BUS_VAL]
 , VHB.[DRIVER_VAL] AS [DRIVER]
 --, VHB. [PCI_VAL]
 --, VHB.[KEY_VAL]
 , VHB.[DEVICE_VAL] AS [DEVICE]
 , VHB.[MODEL_VAL] AS [MODEL]
 --,[HOST_ID]
 , VHB.[VPX_TYPE]
FROM
 [dbo].[VPX_HOST_BUS_ADAPTER] AS VHB WITH (NOLOCK,NOWAIT)
  INNER JOIN [dbo].[VPXV_HOSTS] AS VPH WITH (NOLOCK,NOWAIT)
   ON
    VPH.HOSTID= VHB.[HOST_ID]
 LEFT OUTER JOIN [dbo].[VPXV_VMS] AS VPG WITH (NOLOCK,NOWAIT)
   ON
    VPH.HOSTID = VPG.HOSTID
  LEFT OUTER  JOIN  [dbo].[VPXV_RESOURCE_POOL] AS VRP WITH (NOLOCK,NOWAIT)
   ON
    VRP.RESOURCEPOOLID = VPG.RESOURCE_GROUP_ID
  LEFT OUTER JOIN  [dbo].[VPXV_COMPUTE_RESOURCE] AS VCR  WITH (NOLOCK,NOWAIT)
   ON
    VCR.RESOURCEPOOLID = VRP.PARENT_ID
--WHERE
-- VCR.NAME = 'ESXi 910 SQL Cluster 5.0'
 --AND VHB.[STATUS_VAL] = 'online'
GROUP BY
   VPH.NAME
 , VHB.[STATUS_VAL]
 --, VHB.[BUS_VAL]
 , VHB.[DRIVER_VAL]
 --, VHB. [PCI_VAL]
 --, VHB.[KEY_VAL]
 , VHB.[DEVICE_VAL]
 , VHB.[MODEL_VAL]
 --,[HOST_ID]
 , VHB.[VPX_TYPE]

 

Hope that helps.

 


Tuesday, November 22, 2016 - 12:36:40 PM - Brian ODwyer Back To Top

It is possible to gather the controller information for the ESXi hosts and for the VM guests themselves. I will check on that and get back with some code for you. I changed companies in the last few months and no longer have access to a VMware VCDB but I did get this information before in order to make sure that the Vmware para-virtualized controller was in my VM guests connecting to SQL data, tempdb and log drives and not the default LSI. The LSI is fine for non-SQL data, tempdb and log drives.

 

 


Tuesday, November 22, 2016 - 9:55:47 AM - V. Jahns Back To Top

Great work - many thanks...

Touring through the VM data model (v55 and v60), I found also a controller info - but until now no sensefull reference to this. While I am searching I found a doc on the VM site about "Using VirtualCenter DB Views" but this one shows not the complete data model...

How can I complete your "Disk Properties in VMWare for Virtual Hard Disk" example with this detail?

Thanks


Thursday, June 02, 2016 - 2:15:31 PM - Leon Orlov Back To Top

Very nice and informative article.  Thank you for sharing.


Friday, February 20, 2015 - 11:06:31 AM - Traian C Back To Top

Thanks for the quick reply! Meanwhile I figured out that the VPX_ENTITY view is what I need, right after I posted my question here. Looks like in the [VPXV_VMS] view there's a group name which is the leaf level folder where the VM is kept...after this I wanted to get the whole folder structure going up to the root level so I built this function to get the full path starting from the [VPXV_VMGROUPS].[VMGROUPID]

-------------------------------------------------------

CREATE FUNCTION getVMFolders (@this_ID INT)

 RETURNS NVARCHAR(800)

AS

BEGIN

 [email protected] INT,

@str nvarchar(800)

 SET @trg = @this_ID

 SET @str=''

 WHILE ((SELECT PARENT_ID FROM [VPX_ENTITY] WHERE ID= @trg) IS NOT NULL)

BEGIN

SELECT @str='['+NAME+']\'[email protected], @trg=PARENT_ID FROM [VPX_ENTITY] WHERE [email protected]

END

RETURN @str

END

-------------------------------------------------------


Friday, February 20, 2015 - 10:34:14 AM - Brian ODwyer Back To Top

Yes you can find the folders here from the VPXV_ENTITY view.

SELECT
   [ID]
 , [NAME]
 , [TYPE_ID]
 , [ENTITY_TYPE]
 , [PARENT_ID]
 , [PARENT_TYPE_ID]
 , [PARENT_ENTITY_TYPE]
FROM
 [VCDB].[dbo].[VPXV_ENTITY]
WHERE
 [ENTITY_TYPE] = 'VM_FOLDER'
GO

 

It is also found here in VPXV_VMGROUPS which is the view you would join to the VPXV_VMS view to pull the name using the [VMGROUPID] to join on.

SELECT
   [VMGROUPID]
 , [NAME]
 , [PARENTID]
FROM
 [VCDB].[dbo].[VPXV_VMGROUPS]
;

 

Hope that helps you with your needs.

thanks

Brian


Wednesday, February 18, 2015 - 2:05:53 PM - Traian C Back To Top

Excelent info!!! Do you know how to get the folder structure for the VMs, I was looking into that and was wondering if you found a way to get it from VCDB instead of powercli.


Monday, January 19, 2015 - 3:41:01 PM - Brian ODwyer Back To Top

Sorry for the late resposne Andre, I did look and it looks like it does not have any way to tie the entities to the VM guest disk information. No simple way to find it in the DB, you will need to go at it from the OS level. You will have to have get it from WMI using the O/S information. There is not an easy way to match on disk size versus VMDK size, the vmdk has overhead for VMware and then once you cut an NTFS volume it has overhead for the volume and then the internal bytes usabel is then different.


Friday, September 19, 2014 - 7:08:51 AM - Andre van der Lingen Back To Top

Ok, thank you for that.

Andre

 


Thursday, September 18, 2014 - 1:21:24 PM - Brian ODwyer Back To Top

Andre,

I tried that myself but the SQL table, VPX_GUEST_DISK, for guest disk information that comes in the vCenter DB does not have any thing that links back to the VMDK file the disk is on such as the disk label in the VM configuration. You can try matching on size with information but it is harder than it looks since you are looking at VMDK files size and not the NTFS volume size inside the Windows OS. The data in the guest disk size is from the VMtools and it does not know what the underlying VMDK is.

I will take a look again at the TSQL I did before and then look at the entities table to see if they have a linking path there. If I find something I will let you know.

Brian

 


Tuesday, September 16, 2014 - 9:33:31 AM - Andre van der Lingen Back To Top

Hi Brian,

I am searching for a method to correlate the logical drive letter from the Windows OS to the VMDK file. I have seen a lot of PowerCLI scripts using WMI or the Invoke-VMScript. Both require me to have some account with administrative priveledges. (I don’t have that as a vmware administrator on the deployed VM’s)

Both data values are in the database as you have shown in the examples.

With PowerCLI I am able to get both data values separately but not in relation to eachother. (get-vm ).guest.disks cmdlet and the get-harddisk cmdlet output.

It seems so simple to combine the output from the two commands, but it is just not possible.

Unfortunately I am not able to program in Java or C# to use the open VMware API interface either.

Is it possible with a smart SQL query statement?

The data I would like to see is:

VM name - VM Guest Disk drive letter (like c:, d: or e: etc.) - VMDK file path on the datastore

Any help is appreciated.

Andre van der Lingen


Wednesday, April 30, 2014 - 4:03:13 PM - Svetlana Back To Top

Thanks for sharing, Brian!!!


Tuesday, April 29, 2014 - 2:14:57 PM - AZJim Back To Top

This is extremely helpful information.  THANK YOU!


Thursday, April 24, 2014 - 8:49:35 PM - Brian ODwyer Back To Top

Shawn,

I do not disagree with you, I point out specifically that it can cause great harm. I have used it with the nolock,nowait options for several years on versions 4.x and up but if you have an active database or try to write to the database you could have issues. If you are concerned doing a restore onto a different SQL server of the VCDB would allow you to query it without issue. In fact I would suggest this if you are at all concerned, the restored copy if the database would give the same results.

in the past I have used PowerCLI with PowerShell to get information, it was extremely slow and unwieldy but that was prior to version 5.x which should perform better. The crux of the issue is that any use of the SDK or API requires you to authenticate and have access to the information aside from being a programmer in either Java, C# with experience in other areas. So rather than argue about it if there is any concern use a restored backup on a different server and query that. You get the information and test the backup all in one, most of the configuration data is static and thus amenable to using a backup copy.

I do have Steve Jin's book on the VI and VSphere SDK and have used it but regret that I have not learned Java which would make it easier than using VS and C#. I will make an attempt to write a configuration harvester utility but will also continue to use my method of direct query when needed knowing that it has issues.

thanks

Brian


Thursday, April 24, 2014 - 11:51:25 AM - Shawn Dorsey Back To Top

First and foremost I would like to preface this with the following...I am virtualization admin in a very large VMware shop and not a DBA...with that said we have had two instances where a well meaning DBA and developer have corrupted our VC database by directly accessing the database. 

In both cases, VMware support wa kind enough to remind us that there are two tools readily available that are more suitable data access methods...the first is the open API provided by VMware as well as fully available and documented SDK. 

I would offer an encouraing word to learn and use the SDK versus accessing the db directly. 


Wednesday, April 23, 2014 - 12:47:03 PM - Leon Orlov Back To Top

Great article.  Yes, VMs are here to stay! Learn to love them and learn all you can about them.

Cheers


Wednesday, April 23, 2014 - 10:22:51 AM - mick rust Back To Top

Not often that an article changes the way that we manage our environment - absolutely excellent.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools