Changing Server Collation

In order to avoid collation conflict issues with TempDB, all user databases on a SQL Server instance should be set to the same collation.

Temporary tables and table variables are stored in TempDB, that means that, unless explicitly defined, all character-based columns are created using the database collation, which is the same of the master database. If user databases have a different collation, joining physical tables to temporary tables may cause a collation conflict.

For instance you could have a user database with collation SQL_Latin1_General_CP1_CI_AS on a server with collation Latin1_General_CI_AS:

SELECT name,
collation_name,
is_system =
CASE
WHEN name IN ('master', 'model', 'tempdb', 'msdb') THEN 1
ELSE 0
END
FROM sys.databases
ORDER BY is_system DESC, name ASC

Every time you create a temporary table and join it to a permanent table in this database, you have to ensure that the collations match, or you will run into a collation conflict:

The first option must be preferred when possible, because it doesn’t need performing scalar calculations that end up making any index referencing the column virtually useless.

However, both methods require editing the code, which is not always possible. In those cases, changing the server collation is the only possible solution.

In the SQL Server 2000 days, there was a “nice” tool called rebuildm, that recreated the master database and could achieve this task simply. From SQL Server 2005 on, the only tool to rebuild system databases is the setup utility.

Be warned that blogs and forums are filled with bad advice on this topic. The most frequent ones are these:

Change the collation of the model database
TempDB is created from model when the service starts, which makes this method look smart. It’s not! Changing the database collation for model doesn’t change the collation of the objects inside the database: you would end up with a messy template for every new database.

Copy the model database from another instance with the desired collation.
It could work, given that you copy the database from an instance with the exact same @@version. Even if you managed to copy the database, tempdb would have a collation other than those of master and msdb, which would mean moving the original problem among the system databases. Believe me, this is not what you want.

So, why recommend a method that could fail or cause harm to your instance? There is a scenario where this method comes extremely handy, mainly because the documented and supported method fails, which is a big SQL Server 2005 cluster.

One of the biggest innovations in SQL Server 2008 setup concerned the cluster installation: in SQL Server 2005 the setup process was run against all passive nodes and then against the active node for the instance, while SQL Server 2008 setup runs only against the local machine and has to be repeated for each node in the cluster.

Running the setup on all the passive nodes, as SQL Server 2005 setup does, can be a pain in the rear. The more nodes you have, more things can go wrong.

Despite all my efforts, I was unable to run the setup successfully with rebuild database action on a SQL Server 2005 cluster. The log files did not report anything useful for troubleshooting: they were only saying that one of the passive nodes could not complete the setup. I was ready to uninstall the instance and install from scratch when I came across the undocumented –q parameter. I tried (I had nothing to lose, after all) and it worked.

I had a SQL Server 2005 installation where the server was defined with Latin1_General_CI_AI and databases defined with SQL_Latin1_General_CP1_CI_AS (I didn’t install it and don’t know how it got that way). The client was receiving the “Cannot resolve the collation conflict between” error while accessing their application.

I detached the databases defined with the correct collation, shutdown SQL Server and associated services, ran

sqlservr -m -T4022 -T3659 -q”SQL_Latin1_General_CP1_CI_AS”

restarted the server and associated services, reattached the databases, and everything worked perfectly!

Because the “sqlservr.exe -q” option is undocumented, I did my best to document both the behavior of this option (which can be quite the time-saver), and the overall potential scope of impact of doing such a wide-sweeping change: