MSSQL unsupported collation

[networkoperations]

Hello,

Our Jira database is on MSSQL server. Jira was upgraded and now there are errors about unsupported collation. There is no easy way to change collation of MSSQL database. Copy database wizard from MSSQL doesn't change collation. Do you have any suggestions on how to solve unsupported collation issue with MSSQL?

[Brant Schroeder]

Joe,

Had the same issue. We just scheduled a maintenance outage, shut down JIRA, made snapshots and changed the collation.  you can see how to do it here: https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-database-collation#TsqlProcedure

When we brought everything back up everything was good.  We did it in out test environment first.

[Andy Heinzer]

Hi Joe,

Rather than trying to change your existing database, I have found it much easier to simply create a new database with the correct collation, and then import an XML backup that Jira creates of your data.   This way you can correct that collation error.

Please follow these steps to correct this problem:

1. Select a database type/version from our Supported platforms - Atlassian Documentation
2. Create a new blank database per Connecting JIRA applications to a database - Atlassian Documentation, (paying close attention to the correct collation for that database type)
3. Gather an XML backup of your Jira instance. Even if you can't start Jira, you can still find a recent one of these backups in the filesystem of the Jira server. By default this is saved in <jira-home>/export/ folder
4. Stop JIRA,
5. Run the Jira Configuration tool to tell Jira to use the new blank database, save these changes, (if you can't run this, then you can just directly edit the <jira-home>/dbconfig.xml file to make these changes)
6. Start JIRA up again (when Jira starts with an empty database, it automatically launches the setup wizard)
7. Copy the export XML zip file from <jira-home>/export/ into <jira-home>/import/
8. Then you can import the backup using the 'Import your data' link in the setup wizard

By following these steps you can then import your previous data into a supported database with the correct database setup.