QC 10 to ALM 11 Upgrade Diary

The post shows you the detail on how to upgrade the QC 10 to a new machine with existing qcsiteadmin, as
well as migrating huge project (~120GB) to the new machine

Problem

We have a huge QC repository (~120GB) host in a VM environment with can only 4GB RAM made available. Finger cross, we can still 'live' with this very extreme condition. However, it is not something for long term, and at the end, we plan to migrate the QC to a new physical machine with 16GB of RAM.

Here you are my story.

Upgrade highlights:

• Upgrade from Windows 2003 (virtual machine) to 2008 R2 SP1 (physical machine)
• Upgrade from SQL 2005 to SQL 2008
• Communication_security_passphrase problem which cause QC to stop/corrupted (which rollback is not an option) 
• Can't load database; CMSSQLCreator : Unable to create database default_myemptyproject_db; Unable to add user 'td'; [Mercury][SQLServer JDBC Driver][SQLServer]The login already has an account under a different user name.; which lead to fail to create new project in ALM 11
• Facing java.lang.OutOfMemoryError: GC overhead limit exceeded which lead to the need to extend the JBOSS JVM RAM setting
• Error: Failed SQL: /* ~~QC */ sp_updatestats [Mercury][SQLServer JDBC Driver][SQLServer]User does not have permission to perform this action. during last step of project upgrade which cause by incorrect setting of db owner 

Hardware & Software details

QC 10 in detail

• lets called it [M1]
• In VM environment - Windows 2003
• 4GB RAM
• Both QC and Database in one machine
• Windows LDAP configured for QC login
• Database held in SQL 2005
• Database username used: td

ALM 11 in detail

• Lets called it [M2]
• Physical machine - Windows 2008 R2 SP1
• 16 GB RAM
• Both QC and Database in one machine as well (lets assume this)
• Windows LDAP configured for QC login
• Database held in SQL 2008
• Database username used: td, td_db_admin

Solution (Diary content)

Migrate qcsiteadmin from M1 (SQL 2005) to M2 [QL 2008)                                          

• Detach 'dbqcadmin' from M1. Refer to link in case you are new to SQL database migration.
• In [M2], make sure no 'td' or 'td_db_admin' user exists. If yes, please remove it.
• Add user td with Server Roles [public]
• Add user td_db_admin with Server Roles [public], [dbcreator] and [securityadmin]
• Attach qcsiteadmin_db
• Run below query in qc_siteadmin_db
<sqlscript>
EXEC sp_change_users_login 'report'
EXEC sp_change_users_login 'update_one', 'td', 'td'
EXEC sp_changedbowner 'td_db_admin'
</sqlscript>

Note: The above query must be performed. Else, you might failed to setup with user 'td_db_admin' as well as failed update existing scheme. 

Note: I once try to change the owner manually, although the migration is successfully, however, after few minutes run, you will hit into communication_security_passphrase problem. Please be aware and the steps must be performed 

• Install vcredist_x64.exe (in case it is required)
• Install ALM 64 bits
• Run ALM Server Configuration Wizard with administration right

Note: For my case, the user that I used to log-on although has administrator right, however, I failed to proceed with Server Configuration as 'right' not enough. What I did was close the wizard, and fire up the wizard with administration right again, and then it works!

• Proceed with ALM configuration as per your preference.
• Points to take note
• Setup with user 'td_db_admin'
• Choose existing schema
• Start up IE with administration right. 
• In case you prompted to loosen the right for IE, proceed with below
• Tool -> Internet Option -> Security -> Internet
• Custom Level, enabled ALL (easy)
• Access to http://localhost:8080/qcbin/ - Administration 
• Install any .cab needed
• Successfully log-on.
• Congratulation! You have just successfully migrated the qcsiteadmin.

Migrate QC project from M1 to M2                                                                                                                              

• Pre-requisite: Deactivate the project so that we can proceed with
• Copy (rich copy) of Repository
• Copy (detach) of database for project
• Create an empty project and named it [MyEmptyProject] for Default.
• Attach db xxx_xxxxxxxxxx
• Run below query in xxx_xxxxxxxxxx
<sqlscript>
EXEC sp_change_users_login 'report'
EXEC sp_change_users_login 'update_one', 'td', 'td'
EXEC sp_changedbowner 'td_db_admin'
</sqlscript>

Note: The above query must be performed. Else, you will failed to migrate the project.

Note: When you search for solution at the web, usually this line EXEC sp_changeowner 'td_db_admin' will not be there. However, as we are using td_db_admin as the admin user, the line cannot be skip!

• Move xxx_xxxxxxxxxx repository to DEFAULT folder.
• Go to [MyEmptyProject]. Make a copy of dbid.xml (as a backup) and edit the dbid.xml for below sections
• <PROJECT_NAME>** Put the project name **</PROJECT_NAME>
• <DB_NAME>** Put the project schema name **</DB_NAME>
• <PHYSICAL_DIRECTORY>** Put the correct path of the repository folder of this project**\</PHYSICAL_DIRECTORY>
• PROJECT_UID>82a311c5-a440-4ecd-97a2-e97331a447XX</PROJECT_UID> where change the last 2 digit 'XX' to any 2 new number, I usually replaced the XX as 99
• PR_SMART_REPOSITORY_ENABLED should be changed from 'Y' to 'N'
• Restore project with the edited dbid.xml in MyEmptyProject. Project shall restore successfully.
• Here come the tricky part where it is FINE TUNE for big repository (~120GB)
• In QC's Administration, 
• Set WAIT_BEFORE_DISCONNECT = -1
• Enable DEBUG module for easy trouble-shooting in case something went wrong
• Server -> xxx_xxxxxxxxxx -> enable DEBUG log level
• De-activate project QuintiqApplicationSuite
• Upgrade ALM to Patch 13 (not necessary for big repository, however we are advice to upgrade the ALM to latest patch before proceed with upgrade.
• Verified that the Admin module is now with 8092
• JBoss fine-tuning (heap memory size)
• Redeploy JBoss with ALM Deployement Wizard (run as administrator)
• Update JBoss heap memory size (it need at least 10GB for my case). How?
• Bring down HP ALM service
• Access to C:\ProgramData\HP\ALM\jboss\bin
• Update InstallJbossService.bat and run.bat to 12288M
• InstallJbossService.bat -uninstall
• InstallJbossService.bat -c "default"
• Bring up HP ALM service
• Verify in logfile on the heap memory size increase

Note: While I googling, some pages didnt mentioned about uninstall and re-install. If this was not done, then your JBOSS heap memory size will remain as before, which is around 1.3GB. Please refer to HP Administration page as it describe clearly in there. Lesson learnt: Official documents are really helpful.

Note: Facing java.lang.OutOfMemoryError: GC overhead limit exceeded will be solved once heap memory size increased.
• Verify Project
• Repair Project
• Upgrade Project
• The upgrade took about 3 hrs, and total file scanned will reach to 17280000!

Lesson learnt 

1. Official documents are helpful. You are advice to 'consume' the doc before proceed
2. HP support is helpful too. 
3. EXEC sp_changedbowner 'td_db_admin' is needed in case you are using td_db_admin

Happy testing!