Changing SQL Server Instance Collation

Working in my home lab over the last couple of evenings, I have been installing some additional SQL Server instances ready for me to install System Center Service Manager. As anyone who has worked with System Center 2012 or 2012 R2 knows, getting your SQL instance collation right is critical. To compound matters, when you think you’ve got an instance setup right, you could end up finding that although one product has the correct collation, another does not.

Working in my home lab over the last couple of evenings, I have been installing some additional SQL Server instances ready for me to install System Center Service Manager. As anyone who has worked with System Center 2012 or 2012 R2 knows, getting your SQL instance collation right is critical. To compound matters, when you think you’ve got an instance setup right, you could end up finding that although one product has the correct collation, another does not.

In my case with Service Manager, making sure you use the correct collation not only effects Service Manager but also potentially your ability to integrate it with other parts of the suite such as Operations Manager. There is a really helpful blog post at http://blogs.technet.com/b/servicemanager/archive/2012/05/24/clarification-on-sql-server-collation-requirements-for-system-center-2012.aspx which not only talks through the SQL collations for System Center but additionally offers up a table of interoperable collations.

Needless to say, I got the collation wrong when installing Service Manager in my lab and I really didn’t want to have to go to the trouble of uninstalling it and re-installing it as not only is that time consuming driving a SQL installation but because I have two instances, one for the Management database and another for the Data Warehouse database I would have had to do it twice.

Luckily for me, I found that it is possible to change the collation of a SQL Server instance after installation. I want to point out that although this is possible to do, I’m not sure I would recommend it for someone in a production environment and I would definitely tell you to back anything relating to that instance up first. Doing this not only drops any user databases but because it causes the master database to be rebuilt, it will lose any customisations or setting changes you have made to that instance since install.

With everything backed up and ready, use the following command to change the collation of the instance.

Z:\setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SCSM /SQLSYSADMINACCOUNTS=RJGLAB\Administrator /SQLCOLLATION=Latin1_General_CI_AS

To break the command down, Z:\setup.exe is the path to the SQL Server setup executable on my server. the INSTANCENAME parameter is where you specify the instance you want to modify the collation for. SQLSYSADMINACCOUNTS is where you specify who will be make a sysadmin on the instance after the rebuild (as remember our master database is going to be reset) and SQLCOLLATION is where you specify the new collation to use.

If your instance is running in Mixed Mode Authentication, you can also provide the SAPWD parameter to specify the password that will be used for the sa account however my instance is in Windows Authentication mode so I don’t need to set or use the sa account.