Change SQL 2008R2 Server Collation Setting on Failover Cluster

Many people believe to change the default SQL Server collation setting from the one chosen when SQL Server was originally installed requires SQL Server to be re-installed again, this is not true from SQL 2005 onwards as I shall explain using SQL 2008R2 (in a cluster configuration) as an example.

To change the default SQL Server collation you can simply rebuild the system databases. When you rebuild the master, model, msdb and tempdb system databases, the databases are actually dropped and recreated in their original location. If a new collation is specified in the rebuild statement the system databases are rebuilt using that collation setting. Any user modifications to theses databases will be lost so it is important to backup any of this you wish to retain, eg SQL Server logins.

If you are running a non-clustered (standalone) SQL Server, you can simply skip step 3.

Step 1: Review the Microsoft MSDN documentation regarding “Before You Rebuild The System Databases” & complete the steps to backup your server configuration.

Step 2: Backup your SQL server logins. (How to Backup/Transfer your SQL Logins)

Step 3: Open Cluster Failover Administrator & put your SQL Server resource offline, I also recommend you pause the secondary node to prevent any accidental failovers & the resource coming online.

Step 4: Login to the active SQL node in the cluster & run the following command:

Note the following:
-The INSTANCENAME parameter takes only the SQL server instance, without any server/virtual name, the default instance will be MSSQLSERVER.
-The SA password must meet strong password requirements
-The SAPWD parameter is required if the instance it to use mixed mode authentication.
-The SQLCOLLATION parameter must be set to a correct SQL Collation: 

Step 5: Apply the required service packs & cumulative updates to bring SQL server back to the correct patch level.

You can also simply change the database collation settings for individual databases, this is sometimes suitable rather than changing the default server collation for the entire SQL server.

If you run the command from “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\” and experience the following error when rebuilding the system databases in SQL 2008R2 “Exception has been thrown by the target of an invocation”, the work around is to run the command using the setup.exe which exists on the local media (installation disc).

Jason Vigus