SQL Server - Changing collation order for an entire cluster

An annoying situation arose today with a project - development machines were using legacy SQL collation orders whilst the production cluster was using a windows collation. As all servers and databases should really use the same collation order, cross database joins and tempdb actions will cause you problems if not , it was decided that we'd change the production cluster to use the SQL_ collation orders!

What should have been a straight forward process actually turned out to be more difficult than expected and I found the following information useful, and largely undocumented;

The process for changing the collation order on a SQL server cluster is as follows;

1. Backup all of your databases and then detach them all
2. Take the SQL services offline in cluster management
3. Slap your SQL install disk into a drive and run the following command from it:

   start /wait setup.exe 
      /qn VS=<cluster name> 
      INSTANCENAME=<sql instance name or MSSQLSERVER for default> 
      REINSTALL=SQL_Engine 
      REBUILDDATABASE=1 
      SAPWD=<new strong SA password> 
      SQLCollation=<new collation order> 
      AdminPassword=<strong password> 
      SQLAccount=<domain\account> 
      SQLPassword=<strong password> 
      AGTAccount=<domain\account> 
      AGTPassword=<domain\account>

4. Bring SQL online in cluster management
5. Restore / re-attach your databases

Your master database etc will have been rebuilt and everything should be fine.

The problem I ran into however was when I used a different location to run setup from than was used in the original installation. Setup kept failing saying that it couldn't find valid setup package for "SQL Standard Edition (64 bit)". After trying lots of different combinations of the above, I discovered that despite running setup.exe from a new location, it actually looks at the registry to discover where it was installed from originally and wants to get the MSI files from there!!

The solution? You could change the registry to point to your new location, but easier is this extra parameter on your command line:

REINSTALLMODE=vomus

This tells the setup not to bother looking at the old location, and use the location where the setup is residing. This 60 minute job ended up taking almost 3 hours in the end - but at least I'll know for next time!