How to fix the collation of a Microsoft SQL Server JIRA database

Still need help?

The Atlassian Community is here for you.

Ask the community

This Knowledge Base article was written specifically for the Atlassian Server platform. Due to the Functional differences in Atlassian Cloud, the contents of this article cannot be applied to Atlassian Cloud applications.

Purpose

If your Microsoft SQL Server database doesn't use the recommended collation you can run this procedure to bring them into line with the

This method may also be used to move a SQL Server Database between two servers while ensuring the collation is correct. Direct database manipulation is not covered by our Atlassian Support Offerings and should be up to your DBAs discretion. 

Our recommended method for migrating databases is as follows

  1. Create a new database with the required collation as per the appropriate documentation in Connecting JIRA to a Database.
  2. Follow our Switching Databases using an XML backup to migrate JIRA from the old databases (with the incorrect collation) to the new one, with the correct collation.

Solution

Before you begin

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

Portfolio for JIRA: For instances that do have Portfolio for JIRA installed, please note on Portfolio Plan missing after updating SQL Server Collation KB article before applying the database update change below.


To run this procedure SQL Server Management Studio needs to be in version 2008, if you are still using SQL Server Management Studio 2005, you will need to upgrade it to at least 2008 or migrate your JIRA database to SQL Server Management  2008 or latest.

Diagnosis

The following line is being thrown in atlassian-jira.log:

****************************************************************************************************
You are using an unsupported mssql collation: SQL_Latin1_General_CP1_CI_AS. This may cause some functionality to not work.
Please use SQL_Latin1_General_CP437_CI_AI or SQL_Latin1_General_CI_AI as the collation instead.
****************************************************************************************************

Run the following query against your old database. If it returns any results, you must adjust the collation.

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 collation_name != 'Latin1_General_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%'

Create the new Database

Firstly, create a new database as per our guidelines. Ensure the collation is set to SQL_Latin1_General_CP437_CI_AI.

Create the Database Tables

We'll create the database tables from your existing database. In SQL Server Management Studio, right click on your old database:

  1. Click Tasks > Generate Scripts...;
  2. In the Choose Objects screen, choose Select specific database objects;
  3. Tick Tables - this selects all the objects in the database, but not the database itself (since we already created a new database);
  4. On the Set Scripting Options click Advanced and adjust the following options:
    1. Set Script USE DATABASE to false;
    2. Set Script Full-Text Indexes to true;
    3. Set Script Indexes to true;
  5. Click Next, then Finish;
  6. Run the script against your new database. At this point, your new database will contain tables, but no data.

Disable "Constraint Checks" before importing

Before we import the data, we must temporarily disable constraint checks against all tables in the database. Execute the following query against your new database:

EXEC sp_MSforeachtable"ALTER TABLE ? NOCHECK CONSTRAINT all"

Import data from the old database

Make sure you have enough disk space on your server before starting this section as we will have two databases with the same size once the export wizard is done.


In SQL Server Management Studio, right click on your old database:

  1. Click Tasks > Export Data...;
  2. In the Choose a Data Source screen, ensure your old database is selected. Then click Next;
  3. In the Choose a Destination screen, provide the connection information for your new database. Usually, this will be the same server, however you can use any SQL Server you can authenticate against. Then click Next;
  4. Choose Copy data from one or more tables or views then click Next;
  5. In the Select Source Tables and Views, ensure all tables are ticked, and that all tables have been highlighted;
  6. With the tables highlighted, click Edit Mappings...
  7. Ensure the correct schema has been selected (dbo is usually the correct schema) and that Enable Identity Insert has been ticked. Click OK;
  8. Click Next and complete the transfer of data. Depending on the size of the database, and the speed of the database server(s) being used, this process will take time.

Re-enable Constraint Checks after importing

Once the import has been completed, you must re-enable constraint checks on the database. Execute the following query against your new database:

EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Check the collation is correct

Run the diagnosis query again, to ensure that there are no rows with the incorrect collation.

Point JIRA to the new database

  1. Stop JIRA.
  2. Backup your JIRA Home Directory and Installation Directory.
  3. Reconfigure the connection to your database:
    1. Open the <jira-home>/dbconfig.xml file
    2. Locate the <url> tag
    3. Change the name of the database (the one after the last slash) to your new database. For example,

      <url>jdbc:jtds:sqlserver://dbserver:1433/ old_db </url>

       would be changed to

      <url>jdbc:jtds:sqlserver://dbserver:1433/ new_db </url>

    4. Save the file
  4. Start JIRA and check everything is working.


Having problems?

Create a new database with the required collation as per the appropriate documentation in Connecting JIRA to a Database.
Follow our Switching Databases using an XML backup to migrate JIRA from the old databases (with the incorrect collation) to the new one, with the correct collation.


Description If your Microsoft SQL Server database doesn't use the recommended collation you can run this procedure to bring them into line
Product Jira
Platform Server
Last modified on Oct 2, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.