Test Preparation - Database Migration

1.  Stop JOC Cockpit
    - In case of JOC Cluster: stop all corresponding JOC Cockpit services      

2.  Database Tools

    The database migration is based on a full backup.

        - For MySQL: the existing database will be updated after the backup.
                        MySQL exports include table-level collation definitions (there is no option to exclude them via mysqldump).
                        If a new database is used instead of updating the existing one, a script-based solution can be used to adjust or remove collation settings from the backup before import.
        - For MSSQL: the existing database is first backed up and renamed, then a new database is created, and afterwards the backup data is imported.
                        This approach is used because keys, indexes, and constraints cannot be reliably adjusted in-place and must be fully dropped and recreated during migration. 
                        Therefore, rebuilding the database from a clean structure ensures a consistent state.

        - The <joc install>/joc_install_tables.cmd|sh script is not used because it not only creates database objects but also inserts initial data into an empty database, which can conflict with the import.

        In addition, the full backup already contains all database objects (e.g. custom tables).
        
    2.1 Export/Backup Database

        MySQL (execute on: Linux Bash or Windows CMD/PowerShell on the MySQL server):
            mysqldump -h 127.0.0.1 -u root -p -P 5306 jobscheduler200 > jobscheduler200.sql
   
        MSSQL with Management Studio (example from 20.2):
            2.1.1   Export
                        - Right-click database -> Tasks -> Generate Scripts
                        - Choose Objects: Script entire database and all database objects
                        - Set scripting options -> Advanced:
                                                            * Script Collation = False
                                                            * Script DROP and CREATE = Script CREATE
                                                            * Script USE DATABASE = False
                                                            * Types of data to script = Schema and data
                        - Save as script file: C:\Users\<User>\Documents\script.sql
                        - Save as: Unicode text
            
            2.1.1   Rename the 'jobscheduler200' database to 'jobscheduler200_backup'

    2.2 Set database binary collation as default for new tables
        MySQL:
            mysql -h 127.0.0.1 -u root -p -P 5306 -e "ALTER DATABASE jobscheduler200 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;"

        MSSQL with Management Studio:
            CREATE DATABASE jobscheduler200 COLLATE Latin1_General_100_BIN2_UTF8;   
            Note: Set database user - see https://kb.sos-berlin.com/display/JS7/JS7+-+Database article for the corresponding database


    2.3 Convert table collations
        MySQL:
            2.3.1   Generate ALTER TABLE statements
                        mysql -h 127.0.0.1 -u root -p -P 5306 -N -e '
                            SELECT CONCAT("ALTER TABLE `", TABLE_NAME,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;")
                            FROM INFORMATION_SCHEMA.TABLES
                            WHERE TABLE_SCHEMA = "jobscheduler200"
                            AND TABLE_TYPE = "BASE TABLE";
                        ' > alter_tables.sql

            2.3.2   Execute generated statements
                        mysql -h 127.0.0.1 -P 5306 -u root -p jobscheduler200 < alter_tables.sql


            2.3.3   Verify result (MySQL):
                        mysql -h 127.0.0.1 -u root -p -P 5306 -t -e "SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'jobscheduler200' AND COLLATION_NAME IS NOT NULL;" > verify_db_columns.txt
   
                        Check verify_db_columns.txt - should show the utf8mb4_bin collation

        MSSQL:
            2.3.1   Import backup into database
                    PowerShell console on the MSSQL server machine:
                        sqlcmd -S localhost -E -d jobscheduler200 -i "C:\Users\<User>\Documents\script.sql"
   
            2.3.2
                    Verify result with Management Studio: should show the Latin1_General_100_BIN2_UTF8 collation
                        SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
                        FROM INFORMATION_SCHEMA.COLUMNS 
                        WHERE TABLE_CATALOG = 'jobscheduler200'
                        AND COLLATION_NAME IS NOT NULL
                        ORDER BY TABLE_NAME, COLUMN_NAME;


3.  Start JOC Cockpit