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!

Extend HP TestResultsDeletionTool.exe for more advanced test result clean up customization by using OTA API with VB scripting

Extend the TestResultsDeletionTool more for advanced deletion customization such as test plan folder/sub folder deletion, test result count constraint deletion etc by using OTA API with VB scripting

Problem

Test executing via Quality Center, 250 testsets, 400 manual scripts which run bi-weekly, and 1000 automated scripts which run nightly leads to tons of test results (since 2010) has been generated and this has indirectly consumes up to GBs of disk spaces in the running environment.

HP's TestResultsDeletionTool can be use to clean up the mess, however I wish to do something more advances like
- Batch cleaning
- Test Plan folder/sub folder cleaning
- Clean up the test result if it has more than 100 counts (to keep results for low frequent execution test cases)

Solution

By using OTA API with VB scripting. VB script can be downloaded by clicking on vbscript.

As TestResultsDeletionTool is using test case as the primary source to delete its run history, hence we need a way to navigate from [Test Case -> Test Set (Test Lab) -> Test Instance -> Run History] to find its way for the clean up

Explanation 1: Below codes is needed to do a cross filter from Test Set to Test Case in order to list out all Test Set that has an instance of Test Case being assigned to.

'filter by test case idSet TestFilter = TestFact.FilterTestFilter.Filter("TS_TEST_ID") = Chr(34) & oTest.ID & Chr(34)            'setup to retrieve test set listSet TestSetFact = objTDConnection.TestSetFactorySet TestSetTreeManager = objTDConnection.TestSetTreeManagerSet TestSetFilter = TestSetFact.Filter            'Set cross filter to filter by test case idTestSetFilter.SetXFilter "TESTSET-TEST", True, TestFilter.Text
'retreive testset listSet TestSetList = TestSetFact.NewList(TestSetFilter.Text)

Explanation 2: Below code is needed to retrieve the Run History count from each test instance in each test of the selected Test Set

For Each itemTestSet In TestSetList    'print("TestID[" & oTest.ID & "], TestSetName[" & itemTestSet.Name & "], TestSetID[" & itemTestSet.ID & "]")    strTestSetName = itemTestSet.Name    Set TSTestFact = itemTestSet.TSTestFactory    Set TestSetTestsList = TSTestFact.NewList("")    'print(TestSetTestsList.Count)    'Get the total count for the particular test case    'In case total count > 100 (as per define in variable), proceed with the deletion    'Else keep the record, as it is not regularly run                     For Each testInstanceItem In TestSetTestsList          If StrComp(testInstanceItem.TestID, oTest.ID, vbTextCompare)=0 Then              Set runFactory = testInstanceItem.RunFactory              Set runFactoryList = runFactory.NewList("")              'print("TestSetName[" & itemTestSet.Name & "], TestID[" & testInstanceItem.TestID & "], Instances[" & testInstanceItem.Instance & "], runFactoryList.Count[" & runFactoryList.Count & "]" )              intTotalCount = intTotalCount + runFactoryList.Count          End If     NextNext

Explanation 3: Code to trigger TestResultsDeletionTool silently. TestResultsDeletionTool will only works if it is running from the QC Server machine. From observation, if you run from remote machine, nothing will be clean up. Please be aware

The VB script has to call from the machine where the QC

Set wcshell = WScript.CreateObject("WScript.Shell")'Call TestResultsDeletionTool.exestrExeTest = " -Test " & Chr(34) & "[QualityCenter] " & oSubjectNode.Path & "\" & oTest.Name & Chr(34)'print("Deleting test results for --> " & oSubjectNode.Path & "\" & strTestSetName)wcshell.Run strExeDefault & strExeTest, 1, True

Lesson learnt 

N/A

Happy testing!