KTA: How to modify CEBPM SETTINGS.XML file(RepositoryDBConnectionString)

Question / Problem: 

After migration of KTA database, I found that RepositoryDBConnectionString of CEBPM_SETTINGS.XML in the database also need to be modified.
How can I do that?

Answer / Solution: 

The below steps can be performed to update the documents DB connection string:

1. Run the below query against the KTA DB to get the current Document DB connection string

select CEBPM_SETTINGS.value('(/CEBPM_Settings/Common_Settings/DataLayerConfiguration/@RepositoryDBConnectionString)[1]', 'nvarchar(max)') from SERVER_DATA

2. Copy the connection string into a notepad file

3. Change the server name in the connection string

4. Use the below query to update the connection string. You will need to replace <<Conn String>> with the connection string from step 4.

UPDATE SERVER_DATA SET CEBPM_SETTINGS.modify('replace value of (/CEBPM_Settings/Common_Settings/DataLayerConfiguration/@RepositoryDBConnectionString)[1] with "<<Conn String>>"')

Note:
Please ensure all DBs are backed before running these steps. 
Please ensure the above steps are tested before running on production.

Applies to: