SQL 2008 – Changing the Default Collation Settings on the SQL Instance – Be Careful!
Technically I have been taking a (small) break from Exchange shenanigans recently with a shift in focus to SQL server 2008. Now I personally classify myself as a “SQL hobbyist” rather than expert, where I fill in within my organisation for an absent SQL Server DBA post.
Therefore this post might seem a little elementary to some of the SQL guru’s out there, but I decided to write about it – as given the amount of time that I have spent on this today I would not wish for someone else to lose 7 hours of their life to the same fate!
On a separate note – I think that SQL is an excellent product – and the tomfoolery that this article is based upon is more about a problem with the vendor of one particular item of software – rather than an issue with SQL server.
Essentially the scenario was as such:
My company had (operative word there) a number of SQL 2000 installations, 4 of which where were clustered instances (Active / Passive), between all of the instances there were (are) around 112 SQL databases of varying degrees of importance to the organisation (ranging from REALLY Important – to “Snooze”). The view was (and is) to consolidate those installations down into fewer SQL instances – and replace the clusters with Database Replication / Mirroring.
Of course another mitigating factor of the project was to get my company off of SQL 2000 and onto a non extended support platform, we decided to skip SQL 2005 and take the jump to SQL 2008 (much to the disgust of many of our SQL application suppliers – whom seem to display amazing levels of ineptitude when asked if they support the latest platform – but this is a rant that is worthy of a post in its own right).
The first set of SQL servers were moved very simply, I have been using a process where I clone the Security Logins and then Backup and Restore the databases to the new destination server, and then give the new server the same IP address as the previous instance (this was a precedent set prior to my arrival – personally I would have used FQDN) which has worked quite nicely.
Until I hit the following:
Using the same method above I moved a number of databases over to the new server – changed the IP address of the new server and began testing the applications. All worked well until I came to the final application which when I fired it up gave me the following error:
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation
I looked at the Database collation settings and found that they were correctly set to “Latin1_General_CI_AI”, however, the default Database Engine Setting was “SQL_Latin1_General_CP1_CI_AS”. No matter what I tried I could not get this application to connect to the database (I suspect that there is a hard coded check within the application software which looks at the SQL Server default Collation – rather than the Database) – what I pain in the backside.
In the end I decided to see if it were possible to change the server’s default Collation settings.
Before I go any further, the general recommendation is that if you find yourself in this position you should consider a reinstall of SQL – the main reason being that there are potential knock on effects for existing Databases if you change the collation. I decided to go with the Collation change because I only had a few Databases on the server, and the time that it would take me to rebuild would have been more than the Collation change and the restore of the Databases.
In order to change the Default Engine Collation in SQL Server 2008 you will need to stop (ALL) MSSQL Server instance(s) and execute the following command from the SQL Server setup media:
setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD="" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /SqlCollation=Latin1_General_CI_AI
Bear in mind that by using the above command you are to all intents erasing and recreating your SQL Server's MASTER database, plus you will invalidate an previous MASTER backups (as they will contain the previous Collation Settings) and the major knock on effect is that your current Login and Security configuration will also disappear. Bear in mind that any connected Databases will be detached from the server. There are also other issues such as backup stats being lost – so be VERY careful when using the above command.
After I had run the above – I recreated all of my Logins and then re-attached my databases to which, now when tested worked fine!
This might help someone along the way – however, I urge you to weigh up you own situation before completing the actions given above