Database Migration Failing for Compliance Database

[fawad_laiq]

Dear Members,

We are trying to upgrade FNMS 2018 R2 to 2022 R2 but Compliance Database migration is failing with below error:

There was an error while attempting to run 'cm-migr1710-migpost.sql'.
Violation of PRIMARY KEY constraint 'PK_AssetType'. Cannot insert duplicate key in object 'dbo.AssetType'. The duplicate key value is (Mainframe).
Migration step 10001, part of action set "Adding new Asset Types for Data Storage, Network Appliance and Mainframe"
- Adding 3 static rows to AssetType
The statement has been terminated.

Please find the logs attached. Please note logs are from second attempt.

Kind Regards,

Fawad

[ChrisG · Accepted solution]

This error suggests that:

1. You have a custom asset type named "Mainframe" defined.
2. The migration process tries to add a new built-in asset type named "Mainframe".
3. This fails because of the name conflict.

This is untested, but an approach to explore to see whether it works to handle this is:

1. Before commencing the migration, rename the custom asset type:
   

   UPDATE AssetType
   SET AssetTypeName = 'Custom-Mainframe'
   WHERE AssetTypeName = 'Mainframe'​

2. Perform the database migration

You will then end up with 2 asset types in your system: "Mainframe" and "Custom-Mainframe". If you do NOT have custom properties defined then that could be cleaned up to just use the built-in type as follows:

UPDATE a
SET AssetType = mat.AssetTypeID
FROM Asset a
  JOIN AssetType mcat
    ON mcat.AssetTypeID = a.AssetTypeID
    AND mcat.AssetTypeName = 'Custom-Mainframe',
  AssetType mat
WHERE mat.AssetTypeName = 'Mainframe'

DELETE AssetType
WHERE AssetTypeName = 'Custom-Mainframe'

If you DO have custom properties defined on your custom "Mainframe" asset type then more work would be needed to remove the custom asset type - beyond what I can easily suggest here.

[fawad_laiq]

Dear Chris,

Bravo!

It did work, I have used first statement and seems now migration is going on. Will keep you posted if I encounter any issues.