The SQL Server Collation Bootstrap Collation check doens't check tables and columns

Understanding the problem

The MySQL collation check that we introduced in Confluence 5.5.3 will check the database, table and column collation for invalid collation. When an invalid collation is detected, the upgrade will not be performed; and customers must fix their collation before trying the upgrade again. This is a safe operation, because no upgrades happen if there's an invalid collation - fix the collation with the instructions in the KB.

What's wrong in SQL Server?

The SQL Server check only performs a check on the database level collation - the following query is used:

SELECT DATABASEPROPERTYEX(<DBNAME>, 'Collation') SQLCollation

If the collation of a database has been adjusted to the correct collation (but not the tables and columns) the upgrade will proceed, but customers will hit collation problems between when new tables and indexes are created - because SQL Server does not allow mixed collations between columns involved in an index.

This means that customers must roll back, fix the collation of the SQL Server Database, and then re-try the upgrade again. For large customers, this is a much slower process than halting the upgrade before it has begun (as MySQL does).

Suggested Resolution

In addition to the current database level check, we should use the diagnosis query from the KB:

SELECT object_name(object_id) as TableName, name as ColumnName, collation_name
FROM sys.columns
WHERE collation_name <> 'SQL_Latin1_General_CP1_CS_AS'
AND object_name(object_id) NOT LIKE 'sys%'
AND object_name(object_id) NOT LIKE 'queue%'
AND object_name(object_id) NOT LIKE 'file%'
AND object_name(object_id) NOT LIKE 'spt%'
AND object_name(object_id) NOT LIKE 'MSrep%'

If this query returns any rows, the collation is incorrect, and the upgrade should be halted. The error message should direct users to the linked KB article above (that's the agnostic version, and the most up to date).