Updating database (Failed) - 24.06.10 On-prem

[Goekhan Buelbuel]

Hi everyone,

i wanted to update our WS1 UEM on-premise infrastructure from 24.2.0.18 to 24.06.10, but I get the following error message during the DB update.
 

Output:

024-11-10 21:36:15.524 [INFO]    2024-11-10 20:36:15.220 ... done dbo.DeviceModelFamily.seed.sql
2024-11-10 21:36:15.540 [INFO]     ...dbo.DeviceOperatingSystem
2024-11-10 21:36:15.852 [INFO]    2024-11-10 20:36:15.627 ... done dbo.DeviceOperatingSystem.seed.sql
2024-11-10 21:36:15.868 [INFO]     ...dbo.DeviceManufacturer
2024-11-10 21:36:17.274 [INFO]    2024-11-10 20:36:16.830 ... done dbo.DeviceManufacturer.seed.sql
2024-11-10 21:36:17.274 [INFO]     ...dbo.DeviceModelDetail
2024-11-10 21:36:27.446 [INFO]    .Net SqlClient Data Provider: Msg 468, Level 16, State 9, Line 15171 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
2024-11-10 21:37:52.510 [INFO]    Script execution error.  The executed script:
PRINT (CONVERT (VARCHAR (24), GETUTCDATE(), 121)) + ' ... Starting Post Deployment';

SET NOCOUNT ON;

DECLARE @COMMIT_ID AS NVARCHAR (50) = '39b8ac3482930f0c4d6ad4ae52d9da484968b37c';

DECLARE @BUILD_ID AS NVARCHAR (255) = 'BTA-ACRPACSI9-68';

IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = OBJECT_ID(N'dbo.UpgradeHistory')
                  AND type IN (N'U'))
   AND NOT EXISTS (SELECT 1
                   FROM   dbo.UpgradeHistory)
    BEGIN
        INSERT  INTO dbo.UpgradeHistory (MajorVersion, MinorVersion, Revision, HotFix, Description, StartTime, PerformedBy, CommitID, BuildKey)
        VALUES                         (24, 6, '10', 0, '2406 Contains major feature enhancements', GETUTCDATE(), SUSER_NAME(), @COMMIT_ID, @BUILD_ID);
    END

IF ('$(InstallType)' <> 'U')
    BEGIN
        PRINT 'Disabling Constraints';
        EXECUTE USP_DISABLE_ALL ;
    END

DELETE dbo.DeprecatedObject
WHERE  ObjectName = 'dbo.admin_role_requires_passcode';

EXECU
2024-11-10 21:37:52.510 [INFO]    An error occurred while the batch was being executed.
2024-11-10 21:37:52.541 [INFO]    Updating database (Failed)

---

Can someone reproduce this?

[Groupe Autosécurité]

Hello,

I have the same problem on a fresh installation.

2024-11-12 16:33:33.302 [INFO] .Net SqlClient Data Provider: 
Msg 468, Level 16, State 9, Line 15171 Cannot resolve the collation conflict 
between "SQL_Latin1_General_CP1_CI_AS" and "French_CI_AS" in the equal to operation

The WorkspaceONEUEM database is in SQL_Latin1_General_CP1_CI_AS but the master DB is in French_CI_AS

Does anyone have a solution?

[SKretschmann]

Hello everyone,

we just tried upgrading from 23.10 and got the same Error.

 

[Sascha Mogler]

Hello everyone, we just tried upgrading and encountered the same error.

...if the database server also has the same SQL collation as the DB (SQL_Latin1_General_CP1_CI_AS), then the update will work perfectly.

So wait until there is an official statement.

Edited November 13 by Sascha Mogler

[Simon Frankiewicz]

From what I managed to check, the problem during the update is due to the SQL Server settings, specifically the incorrect collation setting for SQL Server itself. However, I was able to change the SQL Server settings. I used this guide:How to Change Your SQL Collation Settings 

The biggest problem, which seems significant to me, is that most of the on-premise instances are running on a shared SQL Server.

I’ll ask the engineers at Omnissa another question: why don’t older versions, such as version 24.02, show this issue, while version 24.06 reports this problem?

Of course, I understand that the documentation specifies that the collation must be set to SQL_Latin1_General_CP1_CI_AS

[dcorna]

I had the same issue this morning upgrading from 23.06 to 24.06.
We ended up upgrading to 24.02 which worked correctly.
As Szymon mentioned, we couldn't afford to change collation on the whole SQL Server instance, as there are other databases too.

I am wondering the same questions posted by Szymon.
Also, documentation specifies to the set SQL_Latin1_General_CP1_CI_AS at the DB level, but there isn't any collation requirement specified at the instance level
https://docs.omnissa.com/it-IT/bundle/WorkspaceONEUEMInstallationV2406/page/CreatetheWorkspaceONEUEMDatabase.html
https://docs.omnissa.com/it-IT/bundle/WorkspaceONEUEMInstallationV2406/page/DatabaseServerPrerequisites.html

[Simon Frankiewicz]

I encountered this setup back in 2018 while implementing a system for a client who had the collation set to SQL_POLISH_CP1250_CS_AS, which was already problematic back then. At that time, we had to create a dedicated SQL Server cluster.

While Googling this issue, I found a blog about Workspace ONE (Airwatch) where it’s mentioned that both the database and SQL Server are required to be in this collation. But I find it surprising that VMware, and now Omnissa, haven’t updated this in the documentation.

 

Link to site: WS1 SQL Database :: Workspace ONE Knowledge Base

This misleads both the engineer and the customer into thinking there’s a different requirement. With a dedicated SQL Server, it’s not an issue, but it becomes problematic when it's shared. Also, why doesn’t this issue appear in lower versions of UEM?

Additionally, while updating two environments today, I noticed that clients have the collation set to Polish_CI_AS on their SQL Servers, which prevents me from updating the systems to higher versions.

Could someone from Omnissa advise on how I can convince the client to make changes to the SQL Server instance and how we can promote continued use of Workspace ONE UEM products? At the moment, it looks doubtful that the client will want to stick with Workspace ONE UEM.

[Goekhan Buelbuel]

Feedback from Support:

We would like to inform you that this is a known issue with DB upgrade. There is an active request running with Engineering team where the cause of this issue has been identified to be as below:

As part of the changes in "MACOS-4851: Model Seeding Script Changes", String based comparison was introduced in the DeviceModelDetailsSeed file, due to which we have encountered the Collation errors in on-prem environments where the DB collation setting is not the same as the default value.

Prior to this change, we only had integer based comparison in the joins and hence, the DB collation setting would not have mattered. The changes in "MACOS-4851: Model Seeding Script Changes" was backported until 2306.

As part of "AAPP-18070: Add iPhone 16 models to model seed script", the 'COLLATE DATABASE_DEFAULT' was added to all the NVARCHAR columns of temp tables in the device model seed files. This should remediate the collation error and we need to backport this to all the versions that have the changes of MACOS-4851 backported. (2408, 2406, 2402, 2310, 2306)

Currently the teams are engaged on "AAPP-18312" to backport the remediation changes to UEM2406. Once this is done the new scripts will be updated.

There are no steps to be taken from your side. Those are the historical changes that were made. The main point here is that the team is working to build new installer files. Once these are updated you would need to download these from resources portal and re-attempt the upgrade procedure.

Its tough to call out on the ETA as this is under code review.

[LNord]

Have anyone heard anything new about this? Is it resolved yet?

[Fabio]

I had the same issue  upgrading from 23.10 to 24.06 and I'm still waiting for upgrade

Is this issue resolved ?

[Moni_26]

I have received the information that the new full installer and patch installer are both available