Cybersecurity
DevOps Cloud
IT Operations Cloud
In some cases it is necessary to change 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: https://support.microfocus.com/kb/kmdoc.php?id=KM03650007
MSSQL: https://softwaresupport.softwaregrp.com/doc/KM03650008
Windows: <drive>\ProgramData\Micro Focus\ALM\webapps\qcbin\WEB-INF\siteadmin.xml
Linux: /var/opt/ALM/webapps/qcbin/WEB-INF/siteadmin.xml
Existing value: <DbUrl>jdbc:sqlserver://tm-sql2014:1433</DbUrl>
New value: <DbUrl>jdbc:sqlserver:// tm-sql2017:1433</DbUrl>
<DbName>qcsiteadmin_db</DbName>
In the above example the element value is: qcsiteadmin_db
This is the Site Admin db/schema name
For MSSQL use SQL Server Management Studio (SSMS)
For Oracle use Oracle SQL Developer, TOAD, or similar
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));
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://softwaresupport.softwaregrp.com/doc/KM02267685
This utility can be used to update some or all project "dbid.xml" files at once