目的

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

• JIRA の SQL Server 2005 への接続
• JIRA の 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

この手順は、2 つのサーバー間で適切な照合を保持しながら SQL Server データベースを移動するためにも使用できます。 データベースの直接操作はアトラシアンのサポート内容に含まれておらず、データベース管理者がご自身の責任で行う必要があります。 

1. 「Jira をデータベースに接続する」内の手順に従い、必要な照合で新しいデータベースを作成します。
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

ソリューション

はじめる前に

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.

診断

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. 新しいデータベースに対してスクリプトを実行します。この時点で新しいデータベースにはテーブルが含まれますがデータは含まれません。

Disable "Constraint Checks" before importing

データをインポートする前に、データベース内のすべてのテーブルで制約チェックを一時的に無効化する必要があります。新しいデータベースに対して次のクエリを実行します。

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

インポートが完了したら、データベースの制約チェックを再有効化する必要があります。新しいデータベースに対して次のクエリを実行します。

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. 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.

お困りですか?

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.