How to fix the collation of a Microsoft SQL Server database

On this page

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

In newer versions of Atlassian Applications, database requirements 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

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:

Solution

Before you begin

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

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.

Does this apply to you?

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:

  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"

    tip/resting Created with Sketch.

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

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:

  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 application database connection to point to the new database
  3. Start the application 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. Error messages from the transfer process, if any
  3. The most recent application logs from the Home Directory
Last modified on May 30, 2017

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.