How to fix the collation of a Microsoft SQL Server database

目的

In newer versions of Atlassian Applications, database requirements have become more and more stringent. This is to ensure that users get the most consistent experiences when working with content, regardless of the content in their database. 

Microsoft SQL Server では、次のレベルで異なる照合セットがあるため、照合が複雑になる場合があります。

1. データベース レベル
2. カラム レベル

この手順は、2 つのサーバー間で適切な照合を保持しながら SQL Server データベースを移動するためにも使用できます。

ソリューション

はじめる前に

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 database to SQL Server Management  2008 or latest.

また、SQL Server でフルテキストのインデックスを利用している場合、変更を行う前にそれらを削除する必要があります。 

次のクエリを実行して、サーバーでフルテキストのインデックスが有効化されているかどうかを確認します。

USE [mydbname]
SELECT fulltextserviceproperty('isfulltextinstalled') 

If the above query results 1, we will have to identify which tables have indices and delete them (If the outcome is '0', please proceed to next section).

1. Identify which tables have full text indices.

2. テーブルのフルテキストのインデックスを削除します。

3. Also remove the full text catalogue.

該当有無の確認

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 <> '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%'

新しいデータベースの作成

Firstly, create a new database as per the guidelines for your specific application. Ensure the collation is set correctly; as well as any other settings that must be set during database creation.

データベース テーブルの作成

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

1. [Tasks] > [Generate Scripts...] の順にクリックします。
2. [Choose Objects] 画面で [Select specific database objects] を選択します。
3. [Tables] を選択します。これにより、データベースそのものを除く、データベース内のすべてのオブジェクトが選択されます (新しいデータベースを作成済みであるため)。
4. [Set Scripting Options] で [Advanced] をクリックし、次のオプションを調整します。
   
   1. [Script USE DATABASE] を false に設定
   2. [Script Full-Text Indexes] を true に設定
   3. [Script Indexes] を true に設定

5. Click "Next," then "Finish"

   スクリプトを実行する前に、テキストベースのカラムのデータ タイプを国際化されたバージョンに変更する必要があります。これにより、さまざまな照合のデータをインポートできるようになります (Active Objects テーブルの問題も修正されます)。

   テキスト エディタを使い、次の内容を特に変更する必要があります。

   1. varchar > nvarchar
   2. char > nchar
   3. text > ntext

   nvarnchar などの値が発生しないよう、完全置換を行うようにします。

6. Run the script against your new database. At this point, your new database will contain tables with the correct data types and collation, but no data.

インポート前に制約チェックを無効化

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

EXEC sp_MSforeachtable"ALTER TABLE ? NOCHECK CONSTRAINT all"

古いデータベースからデータをインポート

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

1. [Tasks] > [Export Data...] の順にクリックします。

2. [Choose a Data Source] 画面で、古いデータベースが選択されていることを確認します。次に [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. [Copy Data from one or more tables or views] を選択して [Next] をクリックします。

5. In the "Select Source Tables and Views," ensure all tables are ticked, and that all tables have been highlighted

6. テーブルがハイライトされた状態で [Edit Mappings...] をクリックします。

7. 適切なスキーマが選択されていること (通常は DBO が適切なスキーマです) と、[Enable Identity Insert] が選択されていることを確認します。[OK] をクリックします。

8. [Next] をクリックしてデータ転送を完了します。データベースの大きさや、利用されているデータベース サーバーの速度により、このプロセスには時間がかかることがあります。

インポート後に制約チェックを再有効化

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

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

移行が正常に完了したことを確認

1. 診断クエリを再度実行し、誤った照合を持つ行が存在しないことを確認します。

2. Adjust your application database connection to point to the new database

3. Start the application and ensure that everything is working correctly

お困りですか?

アトラシアン サポートに、次の情報を添えてお問い合わせください。

1. 古いデータベースから生成した SQL スクリプト
2. Error messages from the transfer process, if any
3. The most recent application logs from the Home Directory

照合の修正の問題のトラブルシューティングをお手伝いいたします。