How to rapidly change all the database used by ALM

Note: this assumes the new and old databases are of the same type, either both Oracle or both MSSQL. This also assumes db/schema passwords remain consistent. If database passwords are different refer to the following articles…

Oracle: How to quickly change all the password for all ALM related schemas using Oracle

MSSQL: How to quickly change all the password for all ALM related databases using MSSQL when SQL Authentication (mixed-mode) is used

1. Login to the ALM Site Admin
2. Select the "DB Servers" tab
3. The existing (old) database server should be listed
4. Add the new database server
5. Click the “Ping” to make sure the connection is successful
6. Test the new database by creating a new blank project. Do not delete this project or manipulate projects for now. Leave the new project intact as we’ll use it later
7. Note the value of the database name in the far left of the GUI. Copy this to a notepad
8. Make note of the Connection String value. Copy this to a notepad
9. Stop the ALM service
10. Have the DBA backup all the ALM related db/schemas on the source database instance and restore the same on the target database instance
11. Navigate to and open the “siteadmin.xml” file. Typical paths….

Windows: <drive>\ProgramData\Micro Focus\ALM\webapps\qcbin\WEB-INF\siteadmin.xml

Linux: /var/opt/ALM/webapps/qcbin/WEB-INF/siteadmin.xml

12. Backup the file; in case something goes wrong
13. Identify the element: DbUrl
14. Alter the connection string element value by specifying the new value noted in Step 8, for example…

Existing value: <DbUrl>jdbc:sqlserver://tm-sql2014:1433</DbUrl>

New value: <DbUrl>jdbc:sqlserver:// tm-sql2017:1433</DbUrl>

15. Save changes
16. Before closing the siteadmin.xml file make note of the “DbName” element value, for example…

<DbName>qcsiteadmin_db</DbName>

In the above example the element value is: qcsiteadmin_db

This is the Site Admin db/schema name

17. Have the DBA create a backup of the site admin db/schema as noted in Step 15
18. Open a query tool for the database in question. For example…

For MSSQL use SQL Server Management Studio (SSMS)

For Oracle use Oracle SQL Developer, TOAD, or similar

19. Connect to the new database
20. Update the table PROJECTS in the site admin database(noted in step 16) using a correlated sub query which uses the data from the new project created in Step 6…

Oracle (specify the site admin schema name)…

UPDATE <site_admin_schema_name>.PROJECTS SET DB_CONNSTR_FORMAT = (SELECT DB_CONNSTR_FORMAT FROM <site_admin_schema_name>.PROJECTS WHERE project_id = (select max(PROJECT_ID) from <site_admin_schema_name>.PROJECTS));

UPDATE <site_admin_schema_name>.PROJECTS SET DBSERVER_NAME = (SELECT DBSERVER_NAME FROM <site_admin_schema_name>.PROJECTS WHERE project_id = (select max(PROJECT_ID) from <site_admin_schema_name>.PROJECTS));

commit;
 

MSSQL w/SQL Auth…

UPDATE td.PROJECTS SET DB_CONNSTR_FORMAT = (SELECT DB_CONNSTR_FORMAT FROM td.PROJECTS WHERE PROJECT_ID = (select max(PROJECT_ID) from td.PROJECTS))

UPDATE td.PROJECTS SET DBSERVER_NAME = (SELECT DBSERVER_NAME FROM td.PROJECTS WHERE PROJECT_ID = (select max(PROJECT_ID) from td.PROJECTS))

MSSQL w/WinAuth…

UPDATE PROJECTS SET DB_CONNSTR_FORMAT = (SELECT DB_CONNSTR_FORMAT FROM PROJECTS WHERE PROJECT_ID = (select max(PROJECT_ID) from PROJECTS))

UPDATE PROJECTS SET DBSERVER_NAME = (SELECT DBSERVER_NAME FROM PROJECTS WHERE PROJECT_ID = (select max(PROJECT_ID) from PROJECTS))
 

21. Start the ALM service

Next, the "dbid.xml" files for each project will have the old DB_USER_PASS value instead of the new one.
With the new encrypted password in hand, Step 10 above, use the Modify Dbid utility from here: https://support.microfocus.com/kb/kmdoc.php?id=KM02267685
This utility can be used to update some or all project "dbid.xml" files at once