Migrate from ALM Quality Center (Server) to TestRail Server

This repository contains configuration files and scripts to migrate data from ALM Quality Center (ALM QC) to TestRail. The migration occurs at the database level, migrating the data from the ALM QC database into the TestRail database. The migration ELT (Extract-LoadTransform) process uses Docker containers with Airbyte and data transformation scripts, to move data from ALM QC to TestRail.

The migration copies one specified ALM QC project database from an on-premise ALM QC server to one project in an on-premise TestRail Server. Further projects can be migrated from ALM QC to TestRail if needed, and a subsequent migration from TestRail Server to TestRail Cloud can also be performed if needed.

Migration requirements & pre-requisites

1. The TestRail Server instance that is the target of the migration must be already setup and running.
2. macOS or Linux are recommended for the computer running the migration. The migration automation bash scripts in this repository configure and execute the Docker-based migration tooling, and require a Unix-like operating system that is capable of running bash scripts. (Tip: Windows PowerShell with Windows Subsystem for Linux (WSL) should suffice, if it is the only option, but it has not been tested.)
3. Install Docker and docker-compose (which is included with Docker Desktop) on the computer running the migration.

4. Allocate at least 16 gigabytes (GB) of RAM for running the Docker containers. The Docker default is 50% of the computer's RAM, so the default is sufficient on a computer with 32GB+ of RAM. You can adjust the amount of memory allocated to Docker from the Docker Desktop settings.

    

   The ALM QC instance that is the source of the data migration must use SQL Server, not Oracle, as the database.

5. Verify that the computer running the migration has access to the MS SQL Server database of the ALM QC instance that is the source of the migration. You will need:
   • the ALM QC database host
   • the Microsoft SQL Server port (e.g. 1433)
   • the name of the ALM QC database containing all user and project data (e.g. sa)
   • the name of the ALM QC project database(s) to be migrated, which take the form of <domain_name>_<project_name> (e.g. <demo_domain_demo_project))
   • the ALM QC database username (e.g. sa)
   • the password for the ALM QC database user
6. Verify that the computer running the migration has access to the MySQL database of the TestRail instance that is the target of the migration. You will need:
   • the TestRail database host
   • the MySQL port (e.g. 3306)
   • the name of the TestRail database (e.g. testrail)
   • the TestRail database root username (e.g. root)
   • the password for the root TestRail database user
7. Verify that the computer running the migration has access to the Cassandra database of the TestRail instance that is the target of the migration.
   • one or more Cassandra database hosts
   • the Cassandra port (e.g. 9042)
   • the name of the keyspace used by TestRail (e.g. tr-keyspace)
   • a Cassandra database username (e.g. cassandra)
   • the password for the Cassandra database user
8. Ensure you have a spreadsheet application that is capable of viewing .xlsx files, such as MS Excel, Apple Numbers, Google Sheets, or LibreOffice.

Attachment requirements

You will need to provide two paths to attachment file storage locations, one for ALM QC, where the current QC attachment files are located, and one for TestRail, where the attachment files will be copied during the migration.

The ALM QC attachment file storage location can be a mounted volume, or just a local copy of the QC attachment storage.

During the migration process, the ALM QC attachment files will be copied to the TestRail attachment storage location. If the TestRail attachment storage location is a mounted volume, then the attachment copy is complete after the migration. If not, the attachment directory can be copied to the TestRail instance attachment storage after the migration.

Migration usage

Docker and GitHub repository preparation

1. Clone this GitHub repository if you haven't already, with this command:

   git clone git@github.com:gurock/tr-alm-migration

2. Log in to GitHub, and from settings, click "Generate new token" and generate a (classic) personal access token (PAT). You must provide a token name, such as TestRail migration, an expiration, and the following scope:
   • read:packages
3. Click the green "Generate token" button.
4. Be sure to copy and save the personal access token once you've generated it.

5. Use the following command to log in to the GitHub Container Registry (GHCR) with your username and the PAT you just generated:

   export GHCR_PAT=<insert personal access token here>
   export GHCR_USER=<insert GitHub username here>
   echo $GHCR_PAT | docker login ghcr.io -u $GHCR_USER --password-stdin

6. Look for the Login Succeeded message. Now that you are logged in, you'll be able to pull the tr-data-migration image from GHCR,
   by following the steps in the next section.

Container setup

1. Start the Docker container download and setup with the following commands:

   cd tr-alm-migration
   ./run.sh start

2. The script begins by pulling the tr-data-migration image from the GitHub Container Registry (GHCR).
3. At the prompt, enter the path for the ALM QC attachments storage directory.
4. At the prompt, enter the path for the TestRail attachments storage directory.
5. The tr-data-migrationimage is then used to create two Docker containers, one running Airbyte, and the other running the migration data transformations. These Docker containers will be running in the background.
6. Three new directories will be created in your local copy of this repository:
   • /config - Contains configuration files for ALM QC and TestRail.
   • /logs - Log files generated by the migrations you run will be stored here.
   • /reports - Any migration reconciliation reports you generate will be stored here.
7. If any of these directories already exist, they won't be modified by the script.
8. Once you see the message Airbyte containers are running!, the setup script is complete. By default, Airbyte will be running on port 8000.

9. To check the status of the Airbyte and TR ALM QC Docker containers, run the following command:

   ./run.sh status

10. Both containers should have a status of running. You are now ready to configure the migration.

Migration configuration

Run the following command to configure settings for ALM QC, TestRail, and Cassandra:

./run.sh configure

Tip: If either the TestRail database or the ALM QC database is running in a Docker container, you will need to use host.docker.internal in place of localhost or 127.0.0.1 in the configuration files.

Follow the steps below at each prompt to complete the configuration:

ALM QC

1. Enter the URL of the ALM QC database (localhost by default).
2. Enter the MSSQL port (1433 by default).
3. Enter the name of the ALM QC database (DOMAIN_project_dbby default).
4. Enter an ALM QC database username (sa by default).
5. Enter the database user's password.
6. Enter the name of the ALM QC project database.

TestRail

1. Enter the URL of the TestRail database (localhost by default).
2. Enter the MySQL port (3306 by default).
3. Enter the name of the TestRail MySQL database (testrail by default).
4. Enter the name of the root user for the TestRail MySQL database (root by default).
5. Enter the root user's password.

Cassandra

1. Enter one or more Cassandra host URLs, separated by commas (localhost by default).
2. Enter the Cassandra port (9042 by default).
3. Enter the name of the keyspace being used by TestRail (tr_keyspace by default).
4. Enter a Cassandra database username (cassandra by default).
5. Enter the database user's password.

Once done, check the TestRail config and ALM QC config files for the updated settings. You can modify these files directly to make any needed changes.

For an in-depth explanation of the settings within the configuration files, refer to the configuration settings documentation.

Once you see the message, "Successfully set the global variable 'local_infile' to true", you are now ready to migrate the ALM QC data with Airbyte.

Airbyte migration (Extract-Load)

1. Run the following command to start the Airbyte migration:

   /run.sh airbyte

2. This script will extract data from the ALM QC database and load it into newly created Airbyte tables in the TestRail database. These tables, all of which are prefixed with _airbyte_raw_, will later be used to migrate the data into TestRail tables.
3. For informational purposes, you can access the Airbyte UI at the URL and port specified in its configuration. The default URL is localhost:8000.
4. Once you see the message ✅Job finished! with a status of succeeded, the Airbyte migration is complete.

ALM QC migration scripts (Transform)

1. To begin migrating the data corresponding to the project specified in the configuration steps, run the following command:

   ./run.sh migrate

2. Enter migrate at the prompt to confirm that you're ready to migrate the data to TestRail.
3. This will begin the migration process. Each migration script will run in sequence, and will output logs to both the console and to a log file in the /logs directory.
4. Once you see the message ALM QC migration complete!, the migration is finished. The full list of migration scripts are documented in in the QC migration scripts documentation.

5. To migrate attachment files from ALM QC to TestRail (e.g. image files, text files, etc.), run the following command:

   ./run.sh migrate-attachments

6. Confirm that you're ready to migrate the attachment files by entering migrate at the prompt.
7. Once confirmed, the script will copy attachment files from the ALM QC attachment storage location to the TestRail attachment storage location. The script will output logs to both the console and to a log file in the /logs directory.
8. You may see warnings about missing attachment files if they are not found, but the migration will continue without issue if the first copy attempt succeeds.
9. Once you see the message ALM QC attachment migration complete!, the migration is finished.
10. If you need to migrate another project from ALM QC to TestRail, you can re-run the migration scripts with the new project's configuration, starting here.

Reconciliation reporting

1. To generate a migration reconciliation report showing the data that was migrated from ALM QC to TestRail (and the data that was not migrated), run the following command:

   ./run.sh report

2. The script will output a spreadsheet in the /reports directory, showing the reconciliation of data between ALM QC and TestRail. A command to open the report will be displayed in the console (e.g. open ./reports/alm-report-2024-06-28T15:00:00Z.xlsx). The command will open the report in your default spreadsheet application.

Additional information

Cleaning migrated data

1. After you've run the migration scripts, you may choose to remove the migrated data from the TestRail database. To do this, run the following command:

   ./run.sh clean

2. Enter clean at the prompt to confirm that you're ready to remove the migrated data from TestRail.
3. Once confirmed, the script will remove the migrated data from the TestRail database. No data that already existed in TestRail separately from the migration will be removed. The script will output logs to both the console and to a log file in the /logs directory.

Cleaning airbyte data

1. Once you've cleaned the migrated data from TestRail, you may want to remove the Airbyte tables that were created during the Airbyte migration. To do this, run the following command:

   ./run.sh clean-airbyte

2. Enter cleanat the prompt to confirm that you're ready to remove the Airbyte tables from the TestRail database.
3. Once confirmed, the script will remove the Airbyte tables from the TestRail database. The script will output logs to both the console and to a log file in the /logs directory.

Stopping the Docker containers

When you have completed the migration, you will want to stop the migration's Docker containers.

To bring down the Docker containers, run the following command:

./run.sh stop

This will not remove the containers or their data.

Removing the Docker containers

To remove the Docker containers and reset to the initial state, run the following:

./run.sh reset

Enter y for yes at the prompt to confirm that you want to reset the container.

This command will stop and remove the container, and remove the following directories and their contents:

• /config
• /logs
• /reports

Then run:

./run.sh reset-airbyte

Enter y for yes at the prompt to confirm that you want to reset the container.

This command will stop and remove the container.

Additional commands

You can see a full list of available commands by running:

./run.sh help

Some seldom needed additional commands are available.

In some rare circumstances, it may be helpful to run individual data transformation jobs.