By: Brian P ODwyer | Updated: 2014-04-23 | Comments (17) | Related: > 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.
Solution
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.
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.
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.
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 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, 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 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 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.
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
- To learn more about Querying Active Directory Data from SQL Server: Querying Active Directory Data from SQL Server
- To see a document on VMware Database Views go here: Using Database Views
- Learn more about VMware disk storage counters: Datastore / Virtual Machine Storage Capacity Counters
- VMware information on Data Object - VirtualMachineUsageOnDatastore: Data Object - VirtualMachineUsageOnDatastore
- VMware information on Virtual Disk Types: Virtual Disk Types
- VMware information on Thin Provisioning - What's the scoop?: Thin Provisioning - What's the scoop?
- VMware information on Raw Device Mapping - RDM in vSphere 4 and 5 Resources and Experiences: Raw Device Mapping RDM in vSphere 4 and 5 Resources and Experiences
About the author
Brian P ODwyer is a BI Analyst at Cook Children's Health Care System who knows both Infrastructure and SQL serverThis author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-04-23
