SSIS Catalogue Inaccessible After Server Collation Change

Two things to consider:

1. Changing the Collation of an Instance via the -q method changes the Collation for all Databases, not just the system DBs. ( This option is undocumented and hence, unsupported. Please see Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong? for a detailed description of what that option does, as well as the potential scope of impact )

2. SSISDB, distributed by Microsoft, will have the same Collation for every installation since SSISDB is created from a backup file instead of a T-SQL script. There is an implication there that the Collation should probably not be changed. In addition, I found the following thread on the MSDN Forums -- SSISDB collation default: how to change? -- in which a Microsoft employee states:

   We do not support the changing of collation for SSISDB.

   To be fair, it is also possible that a T-SQL install script could specify the Collation for the SSISDB Database, which would then have the same implication of it probably being best to keep that Collation. But, while a T-SQL install script can go either way, a backup being restored will be the Collation that the Database was when it was backed-up (hence the problem that sometimes occurs when restoring something like MSDB from an Instance that had a different default Collation: Example #1 and Example #2).

With those two points in mind:

• You can try to change the Database and columns back to SQL_Latin1_General_CP1_CI_AS via ALTER DATABASE [SSISDB] COLLATE SQL_Latin1_General_CP1_CI_AS and then several ALTER TABLE ... ALTER COLUMN statements.

  PRO: Existing data is not lost, with no need to export / import.

  CON: You could miss one or more columns and the effect(s) might be hard to detect. While there are very few differences between those two Collations, they do not behave exactly the same, and I am not sure what expectations are in the code based on expecting the SQL_Latin1_General_CP1_CI_AS Collation. I'm not sure that I would be comfortable with this, mainly due to lack of certainty.

• You can drop and recreate SSISDB (i.e. delete and re-deploy the catalog). If you have existing packages defined in there, you can export the data to import after the DB is recreated.

  PRO: Know for sure that the issue is fixed.

  CON: Extra work in exporting / importing data (and getting all tables, and in the correct order due to FKs).