SQL Server collation conflict conundrum master vs. content databases (bug?)

Question

SharePoint 2010 standard (MSDN subscription, final release version, release date 22-April-2010) on Windows 2008 R2 standard.  SQL Server 2008 (10.0.2531), two database instances installed and I'm using one called <servername>\SharePoint exclusively for SharePoint 2010.  Actually nothing else on this server (it's a VM).

The master database collation is Latin1_General_CI_AS [Galacian (English - U.S.A.)].  This seems logical, other general server settings likely to be for UK English.

When SharePoint 2010 content databases are created they get collation Latin1_General_CI_AS_KS_WS "because the Latin1_General_CI_AS_KS_WS collation most closely matches the Microsoft Windows NTFS file system collation" according to http://support.microsoft.com/kb/843578 which is saying is required for SharePoint Portal Server 2003, 2007 and I assume this would apply to 2010 as well.

So on my SharePoint 2010 server I create a couple of web applications with their own content databases, creating some "publishing web" sites and adding some pages going through the approval work flow all quite happily... for a while and now suddenly I'm getting exceptions t doing tasks like trying to start the submit-approve workflow, view the list of work flow tasks, adding a new page.  There may be others. The EventLog for Microsoft\SharePoint Products\Shared\Operational seems to have been cleared when I deleted the offending web site earlier so I can't post the exact error message but it was clear that the different collations used between master and site content database were "not acceptable".  I'll post the error message when/if I can repeat the problem.

Any ideas?

I'd hate this problem to return after we're live with the new site.

Answer 1

it's a common gotcha, you really need to set the server collation to the correct value before installing SharePoint to avoid potential character set problems.

http://www.sharepointchick.com/archive/2009/11/16/sql-server-essentials-for-sharepoint-1.aspx

---

Cheers
Spence
www.harbar.net
Microsoft Certified Master | SharePoint 2010
Microsoft Certified Master | SharePoint 2007