Changing the Collation of the Octopus Database

Last updated

By default, the Octopus database is created using Latin1_General_CI_AS collation.

You can change the collation, or create a database initially with a different collation.

A case-insensitive collation (one which has a name containing '_CI_') must be used.

Changing the collation must be done with care.  Changing a SQL Server database's collation does not change the collation of existing user-created objects within.

You must ensure you also change the collation of all objects in the Octopus Database, otherwise errors can occur when modifying the database during Octopus version upgrades.  New objects created will use the updated collation, and when attempting to (for example) perform SQL joins between these and existing objects using the original collation, collation mis-match errors may occur.

For this reason, from Octopus version 3.8 on, when modifying the SQL Server database during Octopus upgrades, Octopus will verify that all columns in the database use the same collation as the database itself.  If they do not, an error will be logged and the upgrade will be prevented from taking place.  This is to ensure you can rollback, or correct the issue and continue, without the database being left in an invalid state.

Errors during Octopus Server upgrades

Database update prevented: One or more columns in the database are not using the default collation

If you have received the error above while upgrading your Octopus Server, it is likely that at some point the collation on your Octopus database was changed without changing the collation of the existing objects.

If you have received the error above, then your database has not been modified and you can safely revert by re-installing your previous version of Octopus Server.

The following SQL can be executed against your Octopus database to identify any columns which do not use the database's default collation:

Identify columns with non-default collation

DECLARE @DatabaseCollation VARCHAR(100)

    @DatabaseCollation = collation_name 
    database_id = DB_ID()

    @DatabaseCollation 'Default database collation'

    t.Name 'Table Name', 'Col Name', 'Type Name',
    sys.columns c 
    sys.tables t ON c.object_id = t.object_id
    sys.types ty ON c.system_type_id = ty.system_type_id    
    t.is_ms_shipped = 0
    c.collation_name <> @DatabaseCollation

(script taken from StackOverflow)

To resolve the issue, either alter the columns reported by the script above to match the database's collation, or alter the database's collation to match the existing columns (assuming all columns are listed).

Some of the issues in changing the collation of an entire database are discussed in this question.