Recently I came across a situation where I have to move my IaaS SQL database connection to a new server. In this post we will see the steps to change the SQL database connection in vRealize Automation.

So, let’s get start.

I have my existing SQL database server hosted on prem-vra-db01, now I have built a new database on prem-vra-db and going to move the db to the newly created database.

Procedure:

• RDP to all IaaS component servers, go to Services.msc and stop all VMware vRealize Automation Services, Agents, and Distributed Execution Managers.
  
• RDP into all IaaS Web component servers and use IIS Manager to stop the VMware vRealize Automation application pools: Repository, vCAC, and WAPI.
  
• Backup your current VMware vRealize Automation SQL database and restore it to the new database server.
  
• Grant the database connection credentials used by the VMware vRealize Automation service dbo and access to the database in the new location. Ensure you can connect to the new SQL server and database from the IaaS server.
  
• Update the configuration file for the Manager Service. In IaaS Manager server, modify below file
  [make sure to take backup of file before change]:
  

“C:\Program Files (x86)\VMware\vCAC\Server\ManagerService.exe.config” as follows:

In the < connectionStrings… section, update Data Source and Initial Catalog with the new details.

Example:
<connectionStrings> <add name=”vcac-repository” providerName=”System.Data.SqlClient” connectionString=”Data Source=NewSQLserver;Initial Catalog=DBname;…

• In IaaS Web component servers, modify the below file [make sure to take backup of file before change]:
  

“C:\Program Files (x86)\VMware\vCAC\Server\Model Manager Web\Web.config” file similar to:

     In the < connectionStrings… section, update Data Source and Initial Catalog with the new details.

Example: 
<connectionStrings> <add name=”vcac-repository” providerName=”System.Data.SqlClient” connectionString=”Data Source=NewSQLserver;Initial Catalog=DBname;… />

• Now in VMware vRealize Automation IaaS MSSQL database on the new server: Update the DynamicOps.RepositoryModel.Models tables to reflect the update configuration file changes:
  

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. Edit this table to replace the Data Source with the new updated server FQDN and the Initial Catalog with your updated database name (if different).

    Execute the following query against the IaaS MS SQL database to check the current configuration for each model:

SELECT * FROM [vcac].[DynamicOps.RepositoryModel].[Models]

Ensure the connection strings in use are the same on the models as used by the configuration files modified previously.

Modify the value in the “ConnectionString” column:

Example:

update [vcac].[DynamicOps.RepositoryModel].[Models] set ConnectionString=’Data Source=prem-vra-db.icdslab.net;Initial Catalog=vcac;Integrated Security=True;Pooling=True;Max Pool Size=200;MultipleActiveResultSets=True;Connect Timeout=200′

• Start the VMware vRealize Automation application pools: Repository, vCAC, and WAPI.
  
• Start the VMware vRealize Automation Services, Agents, and Distributed Execution Managers.
  
• Perform health check, look in the log for any error, and do a machine provision to check everything is running as expected.
  

If you think this post is helpful to you, please like & share. All comments are most welcome for further discussion.