After moving the Change Auditor database to a new SQL Server, the Coordinator starts/stops with the error; Database collation differs from server collation. (4237831)

Consider the scenario of moving the Change Auditor database to a new SQL Server, in this case SQL Server 2014 SP2, and the Coordinator starts/stops with the following errors;

 

 

Use the following registry setting to allow an upgrade to proceed even if it detects a different collation between the two databases.

 

When this is set to 0 (default), you will receive an error indicating that the server collation is different and the upgrade process will stop. When set to 1 the collation switch will be changed to match the new server collation.

 

Registry setting: Allow collation switch

Location            Registry

Path                    HKEY_LOCAL_MACHINE\SOFTWARE\Quest\ChangeAuditor\Coordinator

Value Name     AllowCollationSwitch

Value Type       DWORD

Default               0 - Upon coordinator start, do not allow the detection of a collation switch to proceed.

Value                 0 - Upon coordinator start, do not allow the detection of a collation switch to proceed.

                           1 - Upon coordinator start, allow the detection of a collation switch to proceed.

 

When the ChangeAuditor database collation is changed via the registry key, tables are updated and indexes must be rebuilt. This process may take several hours and is important not to interrupt the process. The upgrade may appear hung at 30% but in fact the collation script is time consuming and will eventually complete. Time required may differ depending on the size of the database. A 40 GB database has been observed to take up to 9 hours for the collation switch to complete. The larger the database, the more time will be required.