Database upgrade from Environment Manager 8.6.x to 10.x fails

Version 3

    Verified Product Versions

    AppSense Environment Manager 10.0AppSense Environment Manager 8.6

    When attempting to upgrade your database from Environment Manager 8.6.x to 10.x via the Server Configuration Portal you may see the following error "This SqlTransaction has completed; it is no longer usable"

     

    Unfortunately, the SCP and Server Configuration Portal logs do not provide any more details about the possible cause.  To get an understand of what part of the database upgrade is failing it is best to upgrade the database via the SQL scripts directly - this should produce a meaningful error that you can work with.

     

    In order to get the SQL upgrade scripts you need to use PowerShell to export them, specifically the API called Export-ApsDatabaseScript. See the example below:

     

    Example

    If upgrading from a version beyond 8.6 GA the correct schema version script needs to be targetted.  This can be obtained by running the following SQL scripts:-

     

    SELECT [Value]
      FROM [Database].[dbo].[Properties]
      WHERE [Name] = 'Version'
    

     

    The version output needs to be placed into the '-AppliesTo' parameter of the script example below

    Failure to export an incorrect schema script will result in a failed upgrade and a non-functional database.

    Export-ApsDatabaseScript -name 'Upgrade Schema 8.6 to 10.1' -AppliesTo <Database Version> –ScriptType UpgradeSchema | Out-File "c:\temp\UpgradeSchema.sql"  #  Only Applies to the schema version supplied in the '-AppliesTo' parameter

     

    See the Server Configuration Portal Scripting Guide for more information and examples of the correct syntax.

     

    In this particular case, when performing the upgrade with the SQL scripts it failed with the following error:

     

    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.AuthorizedUserRole' and the index name 'PK_AuthorizedUserRole'. The duplicate key value is (2a29f6ca-2829-4bb8-89d3-5aed25bfc02c, 1).

    Msg 1750, Level 16, State 1, Line 3816

    Could not create constraint or index. See previous errors.

    The statement has been terminated.

    Upon looking at the pre-upgraded (8.6.x) database, there are duplicate records in the [dbo].[AuthorizedUserRole] table. You can use the following query to identify the affected user.

     

    SELECT [AuthorizedUser].[UserPK], [AuthorizedUser].[Name], [AuthorizedUserRole].[RowGuid] FROM [dbo].[AuthorizedUser]
    INNER JOIN [dbo].[AuthorizedUserRole] ON [dbo].[AuthorizedUserRole].[UserPK] = [AuthorizedUser].[UserPK]
    WHERE [AuthorizedUser].[UserPK] IN (SELECT [UserPK] FROM [AuthorizedUserRole] GROUP BY [UserPK] HAVING COUNT(*) > 1)
    

     

    Once you have the affected user(s) you can remove and re-add them in the 8.6.x Personalization Console before you upgrade - this will resolve the issue. See the below screenshot which shows where in the console you can do this.

     

    The root cause of this issue is a missing primary key constraint on the [AuthorizedUserRole] table. This has already been corrected in version 10 onwards.

     

    If the upgrade stills fails (or the error is slightly different) then please raise a case with Ivanti Support for further investigation.