Summary
Question
How to rapidly change all the database used by ALM
Answer
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
- Login to the ALM Site Admin
- Select the "DB Servers" tab
- The existing (old) database server should be listed
- Add the new database server
- Click the “Ping†to make sure the connection is successful
- 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
- Note the value of the database name in the far left of the GUI. Copy this to a notepad
- Make note of the Connection String value. Copy this to a notepad
- Stop the ALM service
- Have the DBA backup all the ALM related db/schemas on the source database instance and restore the same on the target database instance
- 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
- Backup the file; in case something goes wrong
- Identify the element: DbUrl
- 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>
- Save changes
- 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
- Have the DBA create a backup of the site admin db/schema as noted in Step 15
- 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
- Connect to the new database
- 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))
- 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