Restore old License Usage stats after upgrading Quality Center

Following the upgrade of Quality Center from v.9 to v.10, the client asked whether it will be possible to retain the license usage history after the upgrade.

The upgrade was performed in three main stages, as follows:

  • Migrate QC9.0 projects to a QC10 instance on a temporary server and upgrade all projects
  • Uninstall QC9.0 and install QC10 on the primary server
  • Migrate QC10 Projects back from the temporary server to the primary server
  • Since all the QC instances were using the same database server, we ended up with three siteadmin_db databases. (One for QC9, the second for the temporary QC10 instance and the third for the finsl QC10 instance). This meant that the license usage history of the previous installations would not be visible in the final QC10 instance. To allow them to keep this history, I imported license usage data from QC9.0 into QC10. This was done as follows (using MS SQL as the database):

    The tables involved are:

  • SESSION_LICENSE_HISTORY (Keeps track of the licenses used for each login session)
  • SESSIONS_HISTORY (Tracks the session data, such as project, date and time, etc.)
  • Step 1. Add “QC9” column to license tables:

    This was done so that we could easily differentiate between the old records from QC9 and the records created by QC10. Be sure to add the “NULL” keyword. If this is not done the database will prevent records being added if the field does not have a value, which will result in future user logins failing.

    Step 2. Import license usage data:

    Insert into the QC10 table the contents of the corresponding QC9 table. Note that for the last field (QC9) we insert a value of 1. The first field in both tables contains the user’s session id. Each time a user logs in a new record is added and the value of the SESSION_ID is incremented. The SESSION_ID fields have a unique constraint, which means we can’t simply copy the records from the QC9 table. If we do this, subsequent login attempts may try to add a record to the table with a SESSION_ID value that already exists, causing the login to fail. To get around this we multiply the SESSION_ID of the QC9 records with -1 and use that for the session_id in the QC10 table.

    Step 3. Update project names:

    In most cases the project names will remain the same when going from QC9 to QC10. However, in our case the client wanted to change some of the project names going forward. They also wanted the suffix “_interim” added to the projects while they were hosted on the temporary server. The license history in QC10 only knows about it’s own projects and will therefore ignore all license usage data for logins to projects with other names. We therefore had to update the data that we imported so that the old project names and the “_interim” projects names are changed to the new project names, as follows:

    Once this was done, all historical license usage statistics was now visible in the QC10 Admin console.

    Always rember that changing data directly inside a Quality Center database has the potential to cause serious problems and may compromise the integrity of your poject and/or Quality Center installation. Before making any changes be sure that you have backed up all databases and project repositories.

    Tech tips from JDS