Changing SQL Server Collation on a SQL Server 2008 Failover Cluster

by Posted on
  1. Log-in to the active node.
  2. Trigger a full backup for all System and User Databases.
  3. Script out all log-ins.
  4. If there are any user databases, you will have to export the tables using BCP utility.
  5. Copy SQL Server 2008 install media folder to some local directory. For Eg: D:\SQL2008SP2
  6. Open command prompt and execute below command. Caution: This step will replace your current system databases with new ones with required collation. So please make sure you will be able to restore all logins and the user databases back after this activity.
    D:\SQL2008SP2\Setup.exe /QUIET /ACTION=REBUILDDATABASE
    /INSTANCENAME=MSSQLSERVER
    /SQLSYSADMINACCOUNTS=”DOMAIN\ntid” /SAPWD=strongpassword
    /SQLCOLLATION=New_Collation_Name

    Note: Please replace the values for /INSTANCENAME, /SQLSYSADMINACCOUNTS, /SAPWD and /SQLCOLLATION

    Here you go !. Your SQL server has got new collation settings now. Please let me know if incase you experienced any issues.

  7. Now restore all logins and userdatabases using the scripts generated in step 3 and the tables exported in Step 4.

Reference: http://msdn.microsoft.com/en-us/library/ms179254.aspx


Published by Sherbaz

Technology enthusiast Sherbaz is a Microsoft certified SQL Server Subject Matter Expert. He had fun automating day to day task to achieve management goals. He has extensive hands-on experience in high availability deployments and support with several years of industry experience performing SQL Server installations, configuration, maintenance, backups, performance tuning, Transactional & P2P replication, migration, high availability solutions (Always on, Failover Clustering, Logshipping and Mirroring) and Disaster recovery. Sherbaz also has basic hands-on experience in other DBMSs like MongoDB, Oracle 9i, 10g, Sybase and MySQL. 1 year on Mongodb, Oracle 9i & 10g. Completed M102 & M202 courses in MongoDB. Sherbaz spends his spare time with automation in electronics, embedded system circuits and IOT in his private lab at home. Also, he supports his customers and users at www.SplitExpense.in for issues with app, website or telegram chat bot @SplitExpenseBot https://t.me/SplitExpenseBot. @TheSherBot https://t.me/TheSherBot is another telegram chat bot, partially artificial intelligent, programmed and maintained by him on python and backend Mongodb database. Sherbaz always showed interest learning new technologies and to think out of the box bringing innovation in work. Some of his work is documented @ www.sherbaz.com. He is now busy learning 3D modeling software on his roadmap towards building his next hobby build, A 3D PRINTER. Subscribe to his youtube channel https://www.youtube.com/c/SherbazMohamed/

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.