Confirming and bench testing MSDTC communication for vRA

Products

• VMware Aria Suite

Issue/Introduction

MSDTC connectivity and proper configuration is a requirement for a successful deployment of vRealize Automation (formerly known as VMware vCloud Automation Center). This article provides a PowerShell script and the instructions to test the MSDTC connectivity and performance.

Resolution

To test your MSDTC connectivity and performance:

1. Download the 2117963_sql_distributed_transaction.zip file attached to this article and extract the PowerShell script to all IaaS Model Manager web or Manager Service servers.
2. Open PowerShell from each server as an administrator.
3. Run the script using these arguments:
   Note: Execute the power shell script with the account that you are using to access the SQL DB.  Powershell does a integratedsecurity=true and ignore alternate credentials for remote SQL authentications.

 

Server	SQL Server instance. Defaults to localhost.
User	User to connect to the SQL Server. For this variable you need to use the vRealize Automation service account to ensure you have proper permissions and access to the SQL database. If empty, uses Windows Auth. Defaults to empty (and thus, windows auth).
Password	Password to use, for the user. This value is ignored, if the User argument is not provided or is empty.
Attempts	Number of times to execute the query. Defaults to 101.
Database	Database to use, Defaults to vCaC database. Use -Database parameter to provide an alternate DB name.

The output of this shell script contains the MSDTC performance data. Generally, anything around 100ms is a good time and 500ms indicates an acceptable connection. Anything more than 1 second may indicate possible connection issues or lag that could cause problems with connectivity:

Count	Number of times the query was executed
Average	Average of the query execution times (in milliseconds)
Sum	Total time of query execution for all attempts (in milliseconds)
Maximum	Highest time of query execution (in milliseconds)
Minimum	Smallest time of query execution (in milliseconds)
Property	Not applicable. Can be ignored.

For example: PS c:\test> .\sql_distributed_transaction.ps1 -Server localhost -User sa -Password P@$$w0rd -Attempts 2