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

• Connecting JIRA to SQL Server 2005
• Connecting JIRA to SQL Server 2008
• Connecting JIRA to SQL Server 2012
• Connecting JIRA to SQL Server 2016
• Connecting JIRA to SQL Server 2017
• Connecting JIRA to SQL Server 2019

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. 

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.

If the recommended method for some reason is not suitable for your scenario, please follow the Solution section below to manually fix the collation at the database server side manually

Solution

Before you begin

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 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%'
AND object_name(object_id) NOT LIKE 'sqlagent%'
AND object_name(object_id) NOT LIKE 'plan_persist%'

Create the new Database

Firstly, create a new database as per our guidelines linked above in the 'Purpose' section. It is important that the new database is configured correctly from the start, for example the isolation levels need to be set and the collation should be set to SQL_Latin1_General_CP437_CI_AI or Latin1_General_CI_AI, depending of the the version of supported by JIRA and your preference.

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

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.