I been in a situation with one of my clients where I been implementing and Active/Active clustering setup over SQL Server 2008 R2 as my customer is still using GP 10.0, after finalizing and testing everything I noticed that the code page I must use is 1256 while I have used 1250!!!
Thanks god for having a utilities to update the collation without having to destroy the cluster! I have followed the steps below and it worked like a charm:
1. Backup your objects! As this process will drop logins, linked servers, jobs, and anything related to the instant itself plus will de-attach your database.
2. Make sure that your current server is online and carrying your SQL Instant.
3. Make sure that SQL Services is offline.
4. Run the following command:
"E:\SQL\setup.exe" /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SAPWD=P@ssW0rd /SQLCOLLATION=Arabic_CI_AI /SQLSYSADMINACCOUNTS="DAOUD-PC\DAOUD"
E:\SQL\setup.exe will need to be replaced with the locations of the SQL Server 2008 R2 setup.exe path.
MSSQLSERVER represents the default instance, if you have a named instance this must be changed to hold the instance name.
P@ssW0rd: is the new SA user password, it must be strong password or otherwise the repair will fail.
Arabic_CI_AI: is the new collation name.
DAOUD-PC\DAOUD need to be replaced with the local administrator of the server or PC you are installing the SQL on.
Paste above command into CMD and wait for 3-4 minutes, you will have your SQL Collation changed.
Warning: this operation will recreate your master database, were all existing setting will be reset, if you had databases attached it will be de-attached and will be found in the database folder.