How to fix Collation issue.

[lorenze larot]

Hello,

We have been using JIRA and everything is running well. We just notice that there has been a popup stating that "The Collation health check has been failed in your system" . We are running on version 7.0 for JIRA and MSSQL 2012 for our DB.  I had check the collation that we are using and it is set to "SQL_Latin1_General_CP437_CI_AI". 

I check on this document and is sure that this collation type is supported. https://confluence.atlassian.com/jira/connecting-jira-to-sql-server-2012-610763188.html?continue=https%3A%2F%2Fconfluence.atlassian.com%2Fjira%2Fconnecting-jira-to-sql-server-2012-610763188.html&application=cac

I also tried creating another DB where I'm planing to move my JIRA instance. I did make sure that I set the collation to "SQL_Latin1_General_CP437_CI_AI". when I created the DB. I then run the query below against the newly created database and found out that there are tables that are not in the same collation as what I had set when I created the DB.

use JIRADB03
select object_name(object_id) as tablename, name as columnname,collation_name
from sys.columns
where collation_name is not null
order by object_name(object_id),column_id

I'm not sure if I missed something or if there is any other document that I should be referencing for this issue. Any response is greatly appreciated. 

Thanks,

Lorenze 

[Boris Georgiev [Appfire]]

Here's an article which describes how to fix the collation https://confluence.atlassian.com/jirakb/how-to-fix-the-collation-of-a-microsoft-sql-server-jira-database-776646810.html

[lorenze larot]

Thanks for the Response @Boris Georgiev [Botron]. Does this KB still applies even if running the query below does not return any result?

SELECT object_name(object_id) as TableName, name as ColumnName, collation_name
FROM sys.columns
WHERE collation_name <> 'SQL_Latin1_General_CP437_CI_AI'
AND object_name(object_id) NOT LIKE 'sys%'
AND object_name(object_id) NOT LIKE 'queue%'
AND object_name(object_id) NOT LIKE 'file%'
AND object_name(object_id) NOT LIKE 'spt%'
AND object_name(object_id) NOT LIKE 'MSrep%'

Thanks,

Lorenze

[Boris Georgiev [Appfire]]

are you still seeing the errors in the log or through the health check UI ?

[lorenze larot]

My bad, Its actually returning result, I had it run against another DB.  Another quick question, I'm not sure if this is possible though.

I'm planning to create a new DB base on this KB. https://confluence.atlassian.com/jira/connecting-jira-to-sql-server-2012-610763188.html.  

-Create an xml backup of our system. 

-clone the existing server and reconfigure the DB connection to point to the new DB.

-do an import from the xml back.up.

Do you think this plan will work? Sorry for asking too much.

Thanks,

Lorenze

[Boris Georgiev [Appfire]]

This should work - yes. Just make sure your JIRA instance has enough RAM because if you're importing large backup it might fail with out of memory if your heap is low.

I've imported ~10GB XML backups and everything is fine with the proper memory settings.

https://confluence.atlassian.com/jira/increasing-jira-memory-5896.html

FIRST test the procedure on a test/staging server before doing this on production!