I was faced today with a SQL Server collation issue. The new SQL Server instance we had set up was using the wrong collation at both instance and database/tables/columns. This blog post will talk on how I fixed both issues.
Changing instance level collation
This was the easiest task to do. I followed a well documented method that can be found there. Here is the command I used:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=mssqlserver /SQLSYSADMINACCOUNTS="<MyMachine\SysadminUser>" /SAPWD=<MySysadminPassword> /SQLCOLLATION=Latin1_General_100_CI_AI
As indicated in the command, we're rebuilding the databases here. We have to use a sysadmin user since we're modifying the core database of our instance.
Changing the database collation
This is the most challenging part of the process. It's not enough to change the collation at database level, we have to change all tables/columns/keys/indexes collation to have a complete collation change.
The first technique to use is to clone the database structure and to copy over all the data. One challenge here is to copy data in the right order considering that our database can have complex primary key/ foreign key relationships. We therefore have to proceed at the insertions in the right order. This might be harder when we have a database that has hundreds of tables. This become then a long and tedious task.
Since my time was limited (life is always too short in IT), I had to find a more efficient way of changing the collation of all my databases objects. I did some web searches thinking that I surely not the first one to face this challenge. I did find a script after several web searches that sole my problem. Here is the link to the MSDN article. It is dated to 2006 but collation conversion hasn't changed much since then.
In short, kudos to Igor, he saved me a lot of time and the script ran under 5 minutes in my case. Since I had to convert 30+ databases, all of them were converted in half a day which was tremendous for me and my client!
Thanks again Igor!
Comments