Upgrading Manages Service Provider (MultiTenant) on Prem? - mgsDatabaseUpdate problems

[Steffen]

Hi,

anybody suscessfully updated the Multi Tenant (MSP) to 2019R1?

I could not find current dokumentation on MultiTenant update.

On our single Tenant on Prem TestSystem the update from 2018R1 to 2019R1 was very straight forward.

On production (MT) I ran into problems with

mgsDatabaseUpdate.exe -i InventoryManagerMigration.xml -nsu -d FNMSInventory 

==

on Migration step 14 I have the following error:

There was an error while attempting to run 'im-migr1310-migprep.sql'.
Cannot insert the value NULL into column 'TenantID', table 'FNMSInventory.dbo.Group_MT'; column does not allow nulls.

Any advice would be apprechiated - I did open up a support case though.

Greetings

Steffen

p.s

the "Repair Database Constraint Violations" script of the update manual went through OK

[ChrisG]

I am guessing that this error suggests there is some data in the Group and Organization_MT tables prior to starting the migration process is unable to handle automatically. Your approach of raising a support case to dig deeper is probably the right way to go here, but executing the following queries (on a restored copy of the database containing content from before attempting to run any migration scripts) may get you a step closer to identifying problematic data:

-- All Organization_MT records should have a TenantID
SELECT *
FROM dbo.Organization_MT
WHERE TenantID IS NULL

-- All Group records should have a corresponding Organization_MT record
SELECT *
FROM [Group] g
WHERE NOT EXISTS(SELECT * FROM dbo.Organization_MT o WHERE o.OrganizationID = g.GroupOUID)

Both these queries should return no records; if any records are returned then they may result in an error like you are seeing.

[Steffen]

Thanks a lot Chris, that was a great hint!
Although all statements retuned no data, looking at the tables I found data of an "illegal" TenantID. We removed one Tenant in the past using a script provided by Flexera (Klaus) - and it looks like we have some left over data here...I will keep you updated. 😉

[Steffen]

we did use "KB 000027260 Delete Tenant data for 2018 R1 2019-03-04" which left data in [Organization_MT]

[Steffen · Accepted solution]

After technical deep dive last night we came to the following conclusion

• left over data from TenantDeleten script does not disturb the upgrade process
• there are bugs in the migration scripts
  • FNMSInventory.dbo.Group_MT table did not exist (only Group - withouth "_MT")
  • FNMSCompliance: "duplicate key was found for the object name dbo.SAPActivityCheckWorkTime_MT" - known problem - see FNMS-36776 patch for older versions

so be aware there might be trouble if you want to upgrade MultiTenant (including SAP) from 2018r1 to 2019r1. (Originally we did come from 2017r1MT)

The resore of the DB was successfull and we even gained performance 😉

I count on the Flexera SupportCase now: Case # 01875604 - greetings

Steffen