Sonar data migration post Database collation change

Question

Need to know how we can migrate data post change of database collation change. Basically, below is what I am planning.

1. Get a new DB with new collation which is a requirement for Sonar (Latin1_General_CS_AS). Collation on our existing DB is - Latin1_General_CI_AS
2. Setup the Sonar service and point to the new database with new collation. This will create new tables required for Sonar on the database.
3. Migrate data from old database to new database without changing collation.

I can import data using SELECT INSERT but I ran into issue where Sonar service didn't start post this.(SonarQube service not starting)

We had to truncate and drop tables and re-create them. Can someone advise how we can migrate data?

I am aware of Sonar DB copy tool but I believe this is only for Enterprise version. (http://docs.sonarqube.org/display/SONAR/Sonar+DB+Copy+Tool)

Answer 1

This solution might help you. Basically, it exports information between databases and / or DBMS using simple scripts. BTW, it avoids duplication if projects already exist in the destination.

https://github.com/awltech/sonar-data-migrator

To use, follow below steps.

• Download (or clone) git project
• Run maven install on the project, it will create bineries zip under target/assemly folder.
• Extract zip on machine from where you have access to both sonar server DB.
• Do sonar analysis on both source and target sonar with same code.
• Configure source and target db details in database.properties file
• Run init.sql on target sonar db, it will create temporary tables to store migrated data details
• Set proper java path in script.bat file (you can use script.sh for executing on linux) .

Usage

• Run the bat/sh script passing users as argument for migration of users
• Run the bat/sh script passing data as first argument and project key as per "Key" value in sonar report as second argument. If key is not passed, it will work for all projects in db.

This script will migrate below things:

• Users (default sonar-users role is assigned to all migrated users)
• Action plans associated to the project
• All changes to issue like assignee,severity,status,action plan and resolution
• All reviews/comments added to issue
• All false positive data

My migration was similar to yours (two databases in PostGre), step by step worked! :)