vRealize Automation Data collection doesn’t work

vRealize Automation (vRA), formerly called vCloud Automation Center (vCAC) vSphere Data collection is the process where vRA reaches out to its infrastructure source endpoints and their compute resources to collect information. Below is the number of data collection types:

• Infrastructure Source Endpoint Data Collection
• Inventory Data Collection
• State Data Collection
• Performance Data Collection (vSphere compute resources only)
• vCNS inventory data collection (vSphere compute resources only)
• WMI data collection (Windows compute resources only)
• Cost data collection (compute resources managed by vRealize Business Standard Edition only)

The data collection process happens of a regular basis and can also be started manually from the vRA UI:

vRA -> Infrastructure -> Endpoints -> Endpoints -> Select Compute resource

You can read more about the vRA data collection process here.

Yesterday, 2015-01-12, i got a question about a vRA data collection issue. The following vRA log entry with severity Error could be found:

DatabaseStatsService, ignoring exception, Error executing query usp_SelectAgent Inner Exception, Error  executing query usp_SelectAgentCapabilities

This error usually occurs because of a communication problem between the vRA Infrastructure as a Service (IaaS) component(s) and or its database.

After verifying the virtual machines (VMs) firewall settings (actually turned off) i loked into the Microsoft Distributed Transaction Coordinator Service (MS DTC). This service is responsible for coordinating transactions that span multiple systems and must be enabled on all the servers in the vRA deployment. Port 135 over TCP is used for communication and also a random port between 1 024 – 65 535

Open Component Services by either typing dcomcnfg.exe in Run or a command prompt or window or use Administrative Tools to manage the MS DTC settings.

• Component Services –> Computers –> My Computer –> Distributed Transaction Coordinator.
• Right click on Local DTC & select Properties
• Select Security tab

My customers configuration followed the VMware recommendation

System restart/reboot didn’t solve the problem so MSDTC uninstall and install was the next step since i know these steps has solved similar problems in the past.

• msdtc -uninstall
  Verify the action result via e.g Event Viewer since the command does not give you any output
  
• msdtc -install
  Verify the action result via e.g Event Viewer since the command does not give you any output
  

A MS DTC uninstall and install should be performed if the VMs used by any component in vRA has been cloned from a template or another VM. This is because the MS DTC Globally unique identifier (GUID) configuration does not change while running sys prep meaning you still have the original VMs MS DTC configuration.

When finished the data collection could be manually started and vRA Reservations could be setup.

As mentioned by Adam Bohle in the comments field below another indication of this MS DTC problem is any errors referring to “Failed Transactions” with the DB instance in the All.log or Error.log files in $installation_pathvmwarevcacserverlogs.
Follow the uninstall, install procedure in these cases.