SQL Collation and SharePoint 2007

I will not forget to write this down... Latin1_General_CI_AS_KS_WS

I will not forget to write this down... Latin1_General_CI_AS_KS_WS

Now what was it I needed to write again?

Oh Yeah, I Remember...

Every time I build a SharePoint Farm, be it WSS 3.0- or MOSS 2007-based, I always forget the collation setting for SQL databases being used for SharePoint. There are several places on the web that this information is posted, yet I can never seem to find it in a hurry.

Here is the proper SQL collation for a WSS/MOSS installation:

• Latin1_General Character Set
• Case Insensitive - A and a are treated as the same character
• Accent Sensitive - a and á are NOT treated as the same character
• Kana Sensitive - Japanese Hirakana and Katakana characters which look the same are NOT treated as the same character
• Width Sensitive - Single-Byte and Double-Byte versions of the same character are NOT treated as the same character

What effect does this have in my SharePoint Farm?

Although I do not (yet) know all of the items an improperly selected collation can affect, here is what I do know:

1. Search relies on collation when crawling information in a SharePoint site.
2. There have been reports of improperly configured collation which result in inconsistent behavior such as services not starting correctly.

So what, I can always change it later, right?

Yes, but I can almost guarantee you won't like it - the main databases in a SQL instance reflect the chosen collation. So, in order to change the collation for an instance that will be used for SharePoint, you have to change it on those databases as well.

This involves:

• Backing up the databases
• Documenting all user accounts/permissions
• Dropping the content databases
• Rebuilding the MASTER database
• Restoring the content databases
• Recreating the logins

In short, set this up right before you build your SharePoint Farm. If you are installing SharePoint against an existing SQL backend, consider placing it in its own SQL instance.

Sources

There is a good Blog Entry about it by Areej Abukar on the Jordan SharePoint User Group Blog. Gotta love the Internet.

More detailed information about SQL collation can be found here.