How to rapidly change the database used by ALM

1 Likes

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

  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

  1. Backup the file; in case something goes wrong
  2. Identify the element: DbUrl
  3. 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>
 

  1. Save changes
  2. 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
 

  1. Have the DBA create a backup of the site admin db/schema as noted in Step 15
  2. 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
 

  1. Connect to the new database
  2. 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));

 

  1. 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://softwaresupport.softwaregrp.com/doc/KM02267685
This utility can be used to update some or all project "dbid.xml" files at once

Labels:

How To-Best Practice
Comment List
Anonymous
  • Hi Phil, 

    This is great tips. 

    I looked at the dbid update utility. That is very simple to use as well. Wow! the performance of the tool.

    I think, the tool would add another benefit for the community when they use it, if the tool can do below.

    1) A message after the task is completed.

    2) In Global changes, it's very useful, if you can add "Project Name and DB Name Boxes". It's because sometime like during migration/upgradation you may want to change these 2 parameters. 

Related Discussions
Recommended