Changing SQL Server Collation After Installation and in Production

 

 

In some scenarios we can find different SQL Server collations between the server instance and its databases. Sometimes the collation is fixed in some table columns or inside stored procedures to solve some of relationship problems with columns that have different collations. The most common technique to have a consistent collation for the instance and all it databases has a lot of steps and can sometimes be problematic.

 

A classic example is that when you want to reuse a SQL Server for System Center components and the Collation is not SQL_Latin1_General_CP1_CI_AS (required for the installation)

 

To correct the collations in a few steps is outlined in this Post. It is recommended to create a backup of all databases (including system databases) before take administrative actions on a SQL Server instance. It is important to ensure that there is no fixed collation logic in columns or inside stored procedures, triggers, etc., otherwise the command below may report problems.
In this post I will change  "Latin1_General_CI_AS" collation to "SQL_Latin1_General_CP1_CI_AI"

Step 1 - Determine the SQL Server Collation

Let's confirm the current SQL Server instance collation and all its databases including system databases collation before taking actions.

 

SQL Server Instance Collation

 

SQL Server Master Database Collation

 

 

The server has the "Latin1_General_CI_AS" collation and we'll change it to "SQL_Latin1_General_CP1_CI_AI" in this post.

 

 

Step 2 - Stop the SQL Server Services

First of all, we have to stop the SQL Server Services for the changes to be applied.

 

You can stop the services on the console too. Just Open services.msc and stop the SQL Server service that correspond to your instance.

 

Step 3 - Open Command Prompt and Navigate to Binn Directory

Now we have to open a command prompt with administrative privileges and go to the BINN directory of Microsoft SQL Server, following the example below:

C:\Program Files\Microsoft SQL Server\MSSQL<VERSION>\MSSQL\Binn

 

Step 4 - Apply a New SQL Server Collation

Execute the command below. A lot of information will appears and no user action is required, just close the prompt window after the execution ends.  The parameter "-s" is only necessary if more than one SQL Server instance exists on the target machine. If its successful it will appear "The default collation was successfully changed"

 

sqlservr -m -T4022 -T3659 -s "SQLEXP2014" -q "SQL_Latin1_General_CP1_CI_AI"

 

Parameters used:

 [-m] single user admin mode

 [-T] trace flag turned on at startup

 [-s] sql server instance name

 [-q] new collation to be applied

 

 

Step 5 - Start SQL Server and Verify Collation

Start the SQL Server instance and confirm that all changes were applied on the instance and all databases including system databases have the correct collation.
 

 

SQL Server Instance New Collation

 

SQL Server Master Database New Collation

 

 

Keep in mind this is an undocumented solution and may not be supported by Microsoft if you have issues, so please test carefully before implementing in a production environment.
You can do this procedure for all the instances you have.

See you next time!