Migrate Collate Database | ADM Installation Guide

⚐ Note: Before you start please stop all services pointing to the database and take a backup of the database.

Resources Needed

In the Knowledge base de Aranda can download the package required to perform the migration.

Folder with collatemigr with collatemigr.exe Executable that modifies the collate, data/tables_collation Special collate for some adm boards

Verify that the database is located in a different collate than SQL_Latin1_General_CP1_CI_AI. Right-click on the Database > properties
Verify that the database is on version 9.5.13
Using the sqlpackage.exe verify that the database has no differences at the schema level. For additional information
If there are differences, update the database schema. If you are unable to update the schema, stop the process and contact support.
Run the following script on the database

      SELECT
          id,
          MIN(id) OVER(PARTITION BY product_id, version ORDER BY id) min_id,
          version
      INTO #SOFTWARE_IDS
      FROM (
              SELECT
                      id,
                      product_id,
                      SUBSTRING(version, 1, PATINDEX('%[abcdefghijklmniopqrstuvwxyzz0123456789][^0123456789./()_-]%', version)) version
              FROM AAM_SOFTWARE_VERSION
              WHERE version LIKE '%[^abcdefghijklmniopqrstuvwxyz0-9./ ()_-]%'
              UNION
              SELECT
                      id,
                      product_id,
                      version
              FROM AAM_SOFTWARE_VERSION
              WHERE version in (
                      SELECT
                          SUBSTRING(version, 1,PATINDEX('%[abcdefghijklmniopqrstuvwxyzz0123456789][^0123456789./ ()_-]%', version)) version
                      FROM AAM_SOFTWARE_VERSION
                      WHERE version LIKE '%[^abcdefghijklmniopqrstuvwxyz0-9./ ()_-]%')
      ) t;
  
      UPDATE ds SET
              ds.software_id = si.min_id
      FROM AAM_DEVICE_SOFTWARE ds
      JOIN #SOFTWARE_IDS si ON si.id = ds.software_id;
  
      UPDATE fv SET
              fv.software_id = si.min_id
      FROM AAM_FILE_VERSION fv
      JOIN #SOFTWARE_IDS si ON si.id = fv.software_id;
  
      UPDATE sv SET
              sv.parent_id = si.min_id
      FROM AAM_SOFTWARE_VERSION sv
      JOIN #SOFTWARE_IDS si ON si.id = sv.product_id and sv.parent_id is not null;
  
      UPDATE fr SET
              fr.software_id = si.min_id
      FROM ASM_FILE_RULE fr
      JOIN #SOFTWARE_IDS si ON si.id = fr.software_id;
  
      UPDATE ls SET
              ls.software_id = si.min_id
      FROM ASM_LICENSE_SOFTWARE ls
      JOIN #SOFTWARE_IDS si ON si.id = ls.software_id;
  
      UPDATE cl SET
              cl.software_version_id = si.min_id
      FROM ASM_SOFT_USAGE_CONFIG_LIST cl
      JOIN #SOFTWARE_IDS si ON si.id = cl.software_version_id;
  
      UPDATE sc SET
              sc.software_id = si.min_id
      FROM ASM_SOFTWARE_CATEGORY sc
      JOIN #SOFTWARE_IDS si ON si.id = sc.software_id;
  
      UPDATE fs SET
              fs.software_version_id = si.min_id
      FROM ASM_SOFTWARE_FILE_SUGGES fs
      JOIN #SOFTWARE_IDS si ON si.id = fs.software_version_id;
  
      UPDATE sg SET
              sg.software_id = si.min_id
      FROM ASM_SOFTWARE_GROUP sg
      JOIN #SOFTWARE_IDS si ON si.id = sg.software_id;
  
      UPDATE sr SET
              sr.software_id = si.min_id
      FROM ASM_SOFTWARE_RULE sr
      JOIN #SOFTWARE_IDS si ON si.id = sr.software_id;
  
      DELETE sv
      FROM AAM_SOFTWARE_VERSION sv
      JOIN #SOFTWARE_IDS si ON si.id = sv.id
      WHERE sv.version LIKE '%[^abcdefghijklmniopqrstuvwxyz0-9./ ()_-]%'AND sv.id > si.min_id;
  
      UPDATE sv
      SET sv.version=si.version
      FROM AAM_SOFTWARE_VERSION sv
      JOIN #SOFTWARE_IDS si ON si.id = sv.id
      WHERE sv.version LIKE '%[^abcdefghijklmniopqrstuvwxyz0-9./ ()_-]%'AND sv.id = si.min_id;
      DROP TABLE #SOFTWARE_IDS
  
  

Export a bacpac from the database, this process may take time depending on the amount of data. Right-click on the Database > Task > Export data-tier
Run from a Windows console the following line, from the location of the collatemigr executable
collatemigr -file “[path]\ADMAranda.bacpac”
Route = location of the bacpac
Once the process is completed, import the bacpac again.
Suggestion- You can import the database with a different name than the original if you want to keep an additional backup
Once the import of the database is finished, perform a new import, comparing the information, using Sqlpackage.exe and verify that there are no differences. Otherwise, update the diffs using Sqlpackage.exe, prior to performing the version upgrade on the database.
Once there are no differences, continue with the update of the database