This article details on what steps are to be taken in order to migrate vRA IAAS database from one node to another
There are three places where you need to make a change
ManagerService.exe.config under ...\Program Files (x86)\VMware\vCAC\Server\ ( Manager Service nodes )
Web.Config under ...\Program Files (x86)\VMware\vCAC\Server\Model Manager Web\ ( Web Nodes )
Note : If it's a distributed environment , then ensure you make changes across all nodes which have these components installed.
Stop all IAAS related services across all nodes before starting this activity
Making changes to ManagerService.exe.config
Take a backup of ManagerService.exe.config file
Edit ManagerService.exe.config using word-pad , as seen in the screenshot below. Search for existing database server ,you would find database server name configured under <connectionStrings> , change the value to "Source=<<dbservername>>" to the new SQL server name
3. Save the file
Making changes to Web.config under Model Manager Web
Take backup of Web.config
There would be current database hostname under <connectionString> , edit that to new one and save the file
Making changes inside your IAAS database
Connect to SQL Management Studio and open vRA IaaS database
In the VMware vRealize Automation IAAS database on the new server, update the DynamicOps.RepositoryModel.Models table. This table contains loopback connection strings ( ConnectionString column) for each of the VMware vRealize Automation models that require updating with the new Data Source and Initial Catalog values.
You will need to edit this table to replace the Data Source with your updated server FQDN and the Initial Catalog with your updated database name (if different).
Verify existing values using following query
SELECT * FROM [<<databasename>>].[DynamicOps.RepositoryModel].[Models]
Modify values using following query
update [<<databasename>>].[DynamicOps.RepositoryModel].[Models] set ConnectionString='Data Source=<<newdbservername>>;Initial Catalog=<<databasename>>;Integrated Security=True;Pooling=True;Max Pool Size=200;MultipleActiveResultSets=True;Connect Timeout=200'
Start all IAAS related services across all nodes before starting this activity
Post Change Validations
Perform health-checks across on whole environment. Click Here for detailed instructions
Ensure data-collection is working as expected
Provision a VM and check if it goes through completely
You might encounter following issues
If there are errors seen similar to below stack
Error processing workflow creation Error executing query usp_SearchInitializingRequestVirtualMachines Inner Exception: Error executing query usp_SelectGroup
Then it's a problem with MSDTC. Most likely it would be because of SQL nodes. On SQL node you might see MSDTC exception as well
2018-08-21 05:41:31.620 spid62 Enlist operation failed: 0x8004d01c(XACT_E_CONNECTION_DOWN). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may have been stopped by the client
Reconfigure MSDTC on SQL node , these exceptions should stop occurring again. For MSDTC troubleshooting follow KB 2089503