Sometimes people don’t know what they want, until you start bothering them about it.
In this strictly hypothetical case, you relied on your default collation settings picked by the SQL server installer since no other hints had been given by the client.
This choice of collation was of course wrong!
Now you need to change the instance collation, which pretty much wipes all your instance settings and you have to reconfigure much of it (if not all) since it will rebuild your system databases.
You’ll need the installer available. Simply issue the following command:
setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD=”r@ndomp4ss0wrd” /SQLSYSADMINACCOUNTS=BUILTIN\ADMINISTRATORS mydomain\myuser /SqlCollation=Latin1_General_CI_AI
This will also reset your DB/LOG locations on SQL Server 2008, didn’t happen for my SQL 2012 instance though.
I encountered the following error message with SQL 2012 when running above command:
“The Windows account “Art\Vandelayblabla” does not exist and cannot be provisioned as a SQL Server system administrator.”
Thats because I had encapsulated the sysadminaccounts with citation marks (“), which apparently sucks. So even if you supply multiple accounts you still can’t use “ for encapsulation, really annoying if you are used to *nix standards/common sense.
For multiple accounts just separate them with whitespace (as in my example).
- SQL 2012: http://technet.microsoft.com/en-us/library/dd207003.aspx
- SQL 2008 r2: http://technet.microsoft.com/en-us/library/dd207003(v=sql.105).aspx
- SQL 2008: http://technet.microsoft.com/en-us/library/dd207003(v=sql.100).aspx