When using Microsoft SQL as your database, upgrading from Artifactory v7.49.x to a higher version may cause the following error message upon start up:
2023-07-15T15:02:43.015Z [jfrt ] [ERROR] [c15de720aab63040] [ctoryContextConfigListener:126] [art-init ] - Application could not be initialized: Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CS_AS" in the equal to operation. java.lang.reflect.InvocationTargetException: null
The above error message appears due to the use of an incorrect Database Collation. Based on the error message, the Database collation is set to “Latin1_General_CI_AI” (case insensitive). While Microsoft SQL Database Server is not case-sensitive by default, it is important to select a case-sensitive collation because Artifactory is case-sensitive.
Attempting to update the current Database Collation to the required case sensitive collation (Latin1_General_CS_AI) will not resolve the behavior. The reason being when a database collation is changed, only new tables will inherit the new database collation by default. As an alternative to convert existing data to the new collation, we may perform the following. Please note, when altering or updating the database, it is always advised to adhere to database best practices and take backups before proceeding:
1. Convert data in-place. To convert the collation for a column in an existing table, see Set or Change the Column Collation. This operation is easy to implement, but may become a blocking issue for large tables and busy applications. See the following example for an in-place conversion of the MyString column to a new collation:
ALTER TABLE dbo.MyTable
ALTER COLUMN MyString VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8; 2. Copy data to new tables that use the new collation, and replace original tables in the same database. Create a new table in the current database that will inherit the database collation, copy the data between the old table and the new table, drop the original table, and rename the new table to the name of the original table. This is a faster operation than an in-place conversion, but may become a challenge when handling complex schemas with dependencies such as Foreign Key constraints, Primary Key constraints, and Triggers. It would also require a final data synchronization between the original and the new table before the final cut-off, if data continues to be changed by applications. See the following example for a "copy and replace" conversion of the MyString column to a new collation: CREATE TABLE dbo.MyTable2 (MyString VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8); INSERT INTO dbo.MyTable2 SELECT * FROM dbo.MyTable; DROP TABLE dbo.MyTable; EXEC sp_rename 'dbo.MyTable2', 'dbo.MyTable';
3. Copy data to a new database that uses the new collation, and replace the original database before connecting the new database with Artifactory. Create a new database using the new collation, and transfer the data from the original database via tools like Integration Services or the Import/Export Wizard in SQL Server Management Studio. This is a simpler approach for complex schemas. It would also require a final data synchronization between the original and the new databases before the final cut-off, if data continues to be changed by applications.
When working with complex schemas, it is recommended to perform Option #3, as it is a fast and simple approach to resolve the database collation issue.