Jira Team,
We are planning to upgrade Jira from Jira 6.1.7 to Jira 7.0.11 and then to 7.13.1. In this process we are facing some issues related to collation. We would like to have your support in addressing this issue.
We took a current Jira 6.1.7 production backup from our existing production server and restored on SQL server 2008 R2 and upgraded to 7.0.11. This upgrade went well and didn’t face any issues. Later we built a new SQL server 2016 for Jira upgrade from 7.0.11 to 7.13.1. We took a backup of Jira 7.0.11 database and restored on SQL 2016. We were able to connect to the database but collation at the health check level is failing.
Later we found that our existing collation is SQL_Latin1_General_CP1_CI_AS and at the database level to SQL_Latin1_General_CP437_CI_AI. This change has been successful at the database level on the new SQL server 2016. But still were getting errors during the health check:
Problems detected
Error
Database
Error
Collation
What does this check do?
Checks if the database and tables are configured using the required collation.
Result
The table collation: 'SQL_Latin1_General_CP1_CI_AS' is unsupported by Jira. The Database collation: 'SQL_Latin1_General_CP437_CI_AI' is supported by Jira.
ALTER DATABASE CURRENT COLLATE SQL_Latin1_General_CP437_CI_AI ;
ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON;
Upon researching online, we found that collation needs to be changed at column level too. We came across an article from Jira Wiki to fix the collation of SQL server database.
https://confluence.atlassian.com/kb/how-to-fix-the-collation-of-a-microsoft-sql-server-database-744326177.html
As said, In the article we tried to build a new database with the supported collation SQL_Latin1_General_CP437_CI_AI and executed Steps 1-5 successfully which are listed under creating the database tables. When we tried to run these scripts with sysadmin access, We are able to create dbo schema tables but not the Jiraschema tables
