How to fix the collation of a Microsoft SQL Server database
プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。
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.
Collation in Microsoft SQL Server can be complicated because you can have a separate collation set at:
- データベース レベル
- カラム レベル
This method may also be used to move a SQL Server Database between two servers while ensuring the collation is correct.
Setup Guides for Microsoft SQL Server
To setup your Microsoft SQL Server database correctly, see the following resources for each product:
データベースの変更を行う場合は必ず事前に可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。
Ensure that you are using the correct collation for your product. The following examples use the collation of
SQL_Latin1_General_CP1_CS_AS. The collation required by your application version may be different. See the "Setup Guides for Microsoft SQL Server" section above for more information about your product and version.
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.
Also, if SQL Server has Full-text indices, we will have to delete them before we proceed with any of the changes.
Run the following query, to check whether the server has full text indices enabled or not.
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).
Identify which tables have full text indices.
Delete full text indices of the tables.
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 <> 'SQL_Latin1_General_CP1_CS_AS' 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%'
Creating the new Database
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.
Creating the Database Tables
We'll create the database tables from your existing database. In SQL Server Management Studio, right click on your old database:
- Click "Tasks" » "Generate Scripts..."
- In the "Choose Objects" screen, choose "Select specific database objects"
- Tick "Tables" - this selects all the objects in the database, but not the database itself (since we already created the new database)
- On the "Set Scripting Options" click "Advanced" and adjust the following options:
- Set "Script USE DATABASE" to false
- Set "Script Full-Text Indexes" to true
- Set "Script Indexes" to true
Click "Next," then "Finish"
Before running the script, you should change the data types for text-based columns to the internationalized versions. This ensures that you'll be able to import data from various collations (and also corrects problems with Active Objects tables)
Specifically, you'll need to use your favorite text editor to change:
Ensure you make a full word replacement, so you don't accidentally end up with values like
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.
Disabling 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"
Importing Data from the Old Database
Please ensure you have completed the previous steps from "Creating the Database Tables" in order to generate the tables before running the below steps to export the data. There have been instances of these steps being missed which results in databases having no primary keys or indexes. It's worth checking a few tables to ensure the indexes and primary keys have been properly created before proceeding.
エクスポート ウィザードが完了すると同じ名前の 2 つのデータベースが作成されるため、このセクションを始める前に十分なディスク容量があることを確認してください。
In SQL Server Management Studio, right click on your old database:
Click "Tasks" » "Export Data..."
In the "Choose a Data Source" screen, ensure your old database is selected. Then click "Next"
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"
Choose "Copy Data from one or more tables or views" then click "Next"
In the "Select Source Tables and Views," ensure all tables are ticked, and that all tables have been highlighted
With the tables highlighted, click "Edit Mappings..."
Ensure the correct schema has been selected (
DBOis usually the correct schema) and that "Enable Identity Insert" has been ticked. Click "OK"
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-enabling 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"
Checking the migration has been completed successfully
Run the diagnosis query again, to ensure that there are no rows with the incorrect collation
Adjust your application database connection to point to the new database
Start the application and ensure that everything is working correctly
- The SQL Script generated from your old database
- Error messages from the transfer process, if any
- The most recent application logs from the Home Directory