Purpose

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:

1. The database level
2. The column level

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.

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 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 in 1, we will have to identify which tables have indices and delete them (If the outcome is '0', please proceed to the next section).

1. Identify which tables have full text indices.
2. Delete full text indices of the tables.

3. Also, remove the full text catalogue. 

Does this apply to you?

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

Creating the new Database

Firstly, create a new database as per our Database Setup for SQL Server guidelines. Ensure the collation is set to SQL_Latin1_General_CP1_CS_AS.

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:

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 the 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"

   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:

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

   Ensure you make a full word replacement, so you don't accidentally end up with values like nvarnchar.

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

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

1. Run the diagnosis query again, to ensure that there are no rows with the incorrect collation

2. Adjust your Confluence database connection (either a datasource in server.xml or confluence.cfg.xml) to point to the new database

3. Start Confluence and ensure that everything is working correctly

Having problems?

Contact Atlassian Support and provide the following information:

1. The SQL Script generated from your old database
2. Any error messages from the transfer process, if any
3. The most recent Confluence logs from the Home Directory

We'll be happy to help you troubleshoot problems by fixing the collation.