Skip to main content

Migrate vRA IAAS DB from SQL Standalone to AAG

 We need to follow set of prerequisite to migrate vRA(7.6) IAAS SQL database to a new AlwaysOn Availability Group (AAG) server.

•    AlwaysOn Availability Group (AAG) is only supported with SQL Server 2016 Enterprise or SQL Server 2017 Enterprise

•    Set DTC_Support = Per_DB in AAG 
NOTE:  Starting with SQL Server 2016 (13.x) Service Pack 2 you can alter an availability group for distributed transactions. For SQL Server 2016 (13.x) versions before Service Pack 2, you need to drop, and recreate the availability group with the DTC_SUPPORT = PER_DB setting. Refer this https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-availability-group-for-distributed-transactions?view=sql-server-ver15

•    vRA 7.6 does not support the default SQL Server 2016/SQL Server 2017 130 compatibility mode. If you separately create an empty SQL Server 2016 database for use with IaaS, use 100 or 120 compatibility mode. Refer https://kb.vmware.com/s/article/2150544.

•    Make sure the MSDTC is configured on all SQL node in the cluster as per KB - https://kb.vmware.com/s/article/2038943

•    Enable TCP/IP protocol for SQL Server. 

•    SQL Server includes a model database that is the template for all databases created on the SQL instance. For IaaS to install correctly, do not change the model database size.

Once we are done with above all prerequisites we can migrate the database following the steps below

1.    RDP to all IaaS component servers, select Start > Administrative Tools > Services and stop all VMware vRealize Automation Services, Agents, and Distributed Execution Managers.
2.    RDP into all IaaS Web component servers.
3.    Use Internet Information Services Manager to stop the VMware vRealize Automation application pools: Repository, vCAC, and WAPI.
4.    Backup your current VMware vRealize Automation SQL database and restore it to the new database server.
5.    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.
6.    Update the configuration file for the Manager Service and Model Manager Web
    1)    Within the IaaS Manager server, modify:

"...\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;...

    2)   Within the IaaS Web component servers, modify:

"\...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;... />

 
7.    Within the VMware vRealize Automation IaaS MSSQL database on the new server:
    1)   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).

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

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

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

1. Modify the value in the "ConnectionString" column:

Example:

UPDATE [vra].[DynamicOps.RepositoryModel].[Models] set ConnectionString="<updated_connection_string>" WHERE ConnectionString like '%User ID%'

Note: In case of cross-domain configuration between SQL Server and the IaaS Windows server components, update the credentials settings portion within the ConnectionString to "Integrated Security=True;" or "Integrated Security=False" if using local SQL authentication.  These values should reflect the same used within the configuration files.

8.    Within the IaaS Manager / Web component servers:
    1)    Start the VMware vRealize Automation application pools: Repository, vCAC, and WAPI.
    2)    Start the VMware vRealize Automation Services, Agents, and Distributed Execution Managers.

Labels:

Comments

Post a Comment

Popular posts from this blog

Deleting stale kubernetes clusters in vCD

Unlike the previous version the CSE 4.x is a stateless appliance and its data is stored in VMware Cloud Director Database.  The cluster creation and deletion compared with CSE 3.x version has improved. Besides, there are some scenarios where the cluster deletion is failing even when the "Force Delete" option is chosen. We can use vCD API explorer to delete it, the following are the API queries you can execute  Under definedEntity POST /1.0.0/entities/{id}/resolve DELETE /1.0.0/entities/{id}
Post a Comment
Read more

Manage RabbitMQ using VCP LCM

I have been working in vCD for quite some time, and most of the implementation engineers or consultants faced issues during the deployment or upgrade of RabbitMQ for the vCD message queuing service. From vCD 10.2.2, we can use the built-in MQTT client instead of RabbitMQ however, for VCD multisite configuration or some 3rd party applications need RabbitMQ, such as Veeam or VMware HCX. Using the VCP LCM, we can create a new RabbitMQ environment or manage an existing environment. The reason for this blog is that none of the VMware documentation has the information that registering an existing RMQ instance is only going to work if the RMQ instance was previously deployed by the VCP LCM (or at least, if it is a similar setup based on a Bitnami RMQ VM). Other RMQ instances (e.g., running in CentOS) are not supported and cannot be imported into the VCP LCM 1.5. I hope this information will be useful for someone who is performing green field deployment or upgrading an existing setup.  
Post a Comment
Read more