Monday, September 1, 2014

Using a Cloned VM as a SQL Server - Gotcha for vCAC Install

Installing the newest version of vCAC in a lab, I ran into an issue I thought only I would encounter - turns out a peer ran into the very same issue a couple of days later so I thought I would post the problem and solution.

In our case we were both installing an new (as yet unreleased) version of vCAC with a separate SQL server for the IaaS database component.  The IaaS Windows server and SQL server were cloned from the same base image.  By the way, this issue isn't related to vCAC or a particular version - you could really see this with other products.  It's a known issue with MSDTC and VM clones.

The installation of the IaaS component goes fine, you can even configure the tenant, add fabric groups, vSphere end points, business groups - but then things get weird.  You will likely see that in your vSphere reservation, the memory, storage and network are basically empty - like nothing has been collected.  In fact, if you go and look at the collection status for the compute resource you will see that the Inventory and State collections are not even showing up as configured (neither "on" nor "off").

Finally, you will see these type of entries in the IaaS vCAC server log (also can see this in the vCAC UI under Infrastructure > Monitoring > Log) -

CollectedDataImportService: Ignoring exception: Error executing query usp_SelectManagementEndpoint Inner Exception: Error executing query usp_SelectEntityProperties
Error processing ping response Error executing query usp_SelectAgent Inner Exception: Error executing query usp_SelectAgentCapabilities
DataBaseStatsService: ignoring exception: Error executing query usp_SelectAgent Inner Exception: Error executing query usp_SelectAgentCapabilities

What has happened is due to the clone of the VM for both IaaS and SQL.  If MSDTC has already been installed, then both VMs will have the same GUID for their MSDTC nodes and the communication will fail.  This assumes you don't have other issues such as firewall configuration problems between the two VMs.  

To correct this, simply uninstall and re-install MSDTC on one of the VMs (I did this on the IaaS server) and restart the affected service (for example vCAC Server service on IaaS or SQL Server).  From an elevated command prompt:

msdtc -uninstall
msdtc -install

Re-configure the MSDTC Security settings as you would for the IaaS install.

That should allow collections to run and your reservations will reflect the correct memory, storage and networking information.

UPDATE - you will need to make sure that MSDTC is configured on both the IaaS server and the SQL server for a distributed install.  (Thanks to Steve Kaplan for pointing this out)

17 comments:

  1. What does this communication look like on the network? is it SQL server traffic? RPC server? what?

    ReplyDelete
    Replies
    1. I don't know. I basically used DTCping to troubleshoot this.

      Delete
  2. Isn't there a way to update the GUID as part of the clone operation so this shouldn't normally happen?

    ReplyDelete
    Replies
    1. You could use Guest Customization in vCenter and add the commands to uninstall/install to the Run Once section. I haven't tried that, however.

      Delete
  3. John,
    Thanks for all of your insights. I believe I am having the same issue but I am wondering if you could provide the steps you took to update the MSDTC security settings ("Re-configure the MSDTC Security settings as you would for the IaaS install."). Thannks!

    ReplyDelete
    Replies
    1. Hi Ryan, the settings are as follows:

      Start > Administrative Tools > Component Services
      In the navigation panel on the right browse to Component Services > Computers > My Computer > Distributed Transaction Coordinator
      In the work panel right click on Local DTC and select Properties
      Select the Security tab
      Click the selections for Network DTC Access, Allow Remote Clients, Allow Remote Administration, Allow InBound, and Allow OutBound
      Click OK to commit.

      Delete
  4. Running into this myself, but I have a IAAS box that is 2012 R2 and our SQL box is an older 2008 R2. So it might be doing the same thing even on boxes not cloned from same box.

    Does the MSDTC settings need to be done on both 2008 and 2012 R2?

    ReplyDelete
    Replies
    1. Yes, both the SQL and IaaS box need to have DTC installed and configured. I have had other people report that they have had the issue on non-cloned instances so I'm really not sure why that is happening.

      Delete
  5. Gotcha, I didn't do it on the SQL box. I must of missed that in the install guide. Let me give that a whirl. PS I think I worked with you a while back John when we were POC'ing this thing (large healthcare company dallas). Finally getting back into it and putting it into prod

    ReplyDelete
    Replies
    1. I do remember! Glad you are moving forward - good luck!

      Delete
  6. Hi John. Great post! I wanted to add another datapoint. I am using vRealize Automation 6.2 in a vCenter 5.5 environment. My SQL 2012 server runs on Windows 2012 R2. My IaaS box is on Windows 2012 R2 as well and both are VMs. They were both installed directly from a DVD, as apposed to being cloned or provisioned from a template, and exhibit the same error as you outlined in the article. Applying the MSDTC to both IaaS and SQL servers, restarting the MSDTC services, and restarting the vRA services on the IaaS VM fixed my issue. Thanks a million!

    ReplyDelete
  7. Thanks alot this did the trick, I also had to restart my VCAC (6.1) Appliance

    ReplyDelete
  8. Thanks for this, I hadn't installed MSDTC on the SQL server. Not sure how I missed that in the install guide. Saved me a headache!

    ReplyDelete
  9. Hey just wanted to say thanks! This saved me a lot of time. Odd that it isn't a KB.

    ReplyDelete
  10. Hi, I am facing the same issue that my memory and storage details are not listing in the reservation. I have installed MSDTC on both of database server and IAAS server. Configured the settings as per the guide and restarted MSDTC service and run the data collection. Still not getting resource details.

    ReplyDelete
  11. Wow, thanks so much for this write-up!!! Was looking everywhere and getting nowhere. I can confirm this can also be an issue in vRA7. After re-installing DTC (SQL and IaaS were clones) I still had the issue. I followed the above advice to use DTCping and discovered the windows firewall (although disabled) was still blocking some DTC traffic. I then had to stop the windows firewall service all together to get past these errors. Thank god for this write-up, as I had already did a complete reinstall only to have this same issue, thought it was just IIS corruption. Thanks for writing it up!

    ReplyDelete
  12. i was facing same issue, however it got resolved after following steps mentioned in this blog. It just that i also had to restart sql server.
    Roshan.C

    ReplyDelete