How to fix the collation of a Microsoft SQL Server Confluence database
In newer versions of Confluence, 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:
- データベース レベル
- カラム レベル
If your Microsoft SQL Server database doesn't use the recommended collation you can run this procedure to bring them into line with the Recommended Database Setup For SQL Server.
This method may also be used to move a SQL Server Database between two servers while ensuring the collation is correct.
データベースの変更を行う場合は必ず事前に可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。
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 Confluence 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 before upgrading to newer versions of Confluence.
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 our Database Setup for SQL Server guidelines. Ensure the collation is set to
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, 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.
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:
- 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 Confluence database connection (either a datasource in server.xml or confluence.cfg.xml) to point to the new database
Start Confluence and ensure that everything is working correctly
Contact Atlassian Support and provide the following information:
- The SQL Script generated from your old database
- Any error messages from the transfer process, if any
- The most recent Confluence logs from the Home Directory
We'll be happy to help you troubleshoot problems fixing the collation.