none
SharePoint SQL Collation - Best practice

    Question

  • Hi All,

    We're putting in a new 2010 farm with a dedicated clustered SQL 2008 R2 backend.

    According to the SharePoint administor's handbook: and from memory at various conferences and discussions, the best practice is to install the SQL instance using Latin1_General_CI_AS_KS_WS so you can create new content databases using SQL and a more suitable model database.

    According to: http://support.microsoft.com/kb/2008668

    "We support any CI collation for the SQL instance (for master, tempdb databases). However we recommend using SQL_Latin1_General_CP1_CI_AS as the instance (master, tempdb databases) collation since that’s where the bulk of our testing has been."

    Is there any updated guidance on this?

    Thursday, April 05, 2012 9:57 AM

Answers

  • We could, as you state, let SharePoint do the job on the databases and leave the collation as default, but it does a fairly poor job for content databases.

    Sorry, I'm not sure I understand what you mean. When SharePoint creates a database, it creates it using the Latin1_General_CI_AS_KS_WS collation as this is required. It doesn't matter what the server collation is; the database is created with the required collation.

    Given that it's still supported i'm going to go with the Latin1_General_CI_AS_KS_WS collation but it'd be nice to have a bit of consistency on this from Microsoft. Lacking that we'll just take our chances.

    The recommendations you've seen (from third parties) about setting the instance collation to Latin1_General_CI_AS_KS_WS are for scenarios where you pre-create the databases in SQL (that is, you don't let SharePoint create them). My assumption is the purpose of setting the instance collation to Latin1_General_CI_AS_KS_WS is to prevent accidentally creating the databases with the wrong collation.

    Microsoft is clear. Set the instance collation to SQL_Latin1_General_CP1_CI_AS (the default collation). They recommend this because this is their most tested collation and it is the easiest collation for them to support. If you have a requirement to use a different collation, then do so. The point is if you don't have a compelling reason then you should leave it alone.

    SharePoint's databases need to use the Latin1_General_CI_AS_KS_WS collation. It doesn't matter what the instance collation is as long as the SharePoint databases use Latin1_General_CI_AS_KS_WS.


    Jason Warren
    Infrastructure Specialist
    Habañero Consulting Group
    www.habaneros.com/blog



    Wednesday, April 11, 2012 3:56 PM
  • There are two different Levels of the collation:

    Collation of SharePoint Databases (like Config_DB, Content_DB, SA_DB ...):
    SharePoint always use Latin1_General_CI_AS_KS_WS as collation and it is not supported to Change this.

    Default Collation of the SharePoint Instance:
    This configuration you have to Change if you install the instance on the SQL box. If you know, you will have only SharePoint in the Instance, you can configure this with Latin1_General_CI_AS_KS_WS. The result of them is that all System databases (Master, tempDB ...) has also the SharePoint collation and we do not loose Performance in SQL internally. Unfortunatelly you cannot Change the Default collation later on.

    I always recommend to use Latin1_General_CI_AS_KS_WS if possible.

    Additional hint: the Article 2008668 is written for SharePoint Server 2007, not for 2010. But this configuration is also recommended for 2010 

    Friday, September 28, 2012 8:13 AM

All replies

  • If I understand you correctly, you want clarification about which collation to use for the SQL Server instance, Latin1_General_CI_AS_KS_WS or SQL_Latin1_General_CP1_CI_AS.

    When SharePoint creates the databases, they are created with the Latin1_General_CI_AS_KS_WS collation. This is automatic, and it happens independent of the server collation, which by default is SQL_Latin1_General_CP1_CI_AS.

    I believe the issue of having SharePoint databases with the wrong collation comes up only if you are creating your databases beforehand. You'll see here that under Database Requirements, all SharePoint databases must be Latin1_General_CI_AS_KS_WS.

    I have to agree with the KB. When Microsoft says something like "In theory all collations of type Y will work, but use collation X because that's the one we have logged the most tests against" it means you should use this unless you have a specific reason not to. It could be a very big deal if you encounter an issue with one of the other less-tested collations. That's not to say they aren't tested (I would hope they are), it's just they're most comfortable with the default collation. To phrase this in another way, if you call up support with a problem and you're not using Latin1_General_CP1_CI_AS as your instance collation, they could make you change this to rule out issues with Latin1_General_CI_AS_KS_WS. 

    If you're letting SharePoint create your databases, I see no reason to change the instance server collation.

    Edit: See also this thread.


    Jason Warren
    Infrastructure Specialist
    Habañero Consulting Group
    www.habaneros.com/blog


    Thursday, April 05, 2012 7:42 PM
  • Thanks Warren,

    We could, as you state, let SharePoint do the job on the databases and leave the collation as default, but it does a fairly poor job for content databases.

    Given that it's still supported i'm going to go with the Latin1_General_CI_AS_KS_WS collation but it'd be nice to have a bit of consistency on this from Microsoft. Lacking that we'll just take our chances.

    Wednesday, April 11, 2012 2:12 PM
  • We could, as you state, let SharePoint do the job on the databases and leave the collation as default, but it does a fairly poor job for content databases.

    Sorry, I'm not sure I understand what you mean. When SharePoint creates a database, it creates it using the Latin1_General_CI_AS_KS_WS collation as this is required. It doesn't matter what the server collation is; the database is created with the required collation.

    Given that it's still supported i'm going to go with the Latin1_General_CI_AS_KS_WS collation but it'd be nice to have a bit of consistency on this from Microsoft. Lacking that we'll just take our chances.

    The recommendations you've seen (from third parties) about setting the instance collation to Latin1_General_CI_AS_KS_WS are for scenarios where you pre-create the databases in SQL (that is, you don't let SharePoint create them). My assumption is the purpose of setting the instance collation to Latin1_General_CI_AS_KS_WS is to prevent accidentally creating the databases with the wrong collation.

    Microsoft is clear. Set the instance collation to SQL_Latin1_General_CP1_CI_AS (the default collation). They recommend this because this is their most tested collation and it is the easiest collation for them to support. If you have a requirement to use a different collation, then do so. The point is if you don't have a compelling reason then you should leave it alone.

    SharePoint's databases need to use the Latin1_General_CI_AS_KS_WS collation. It doesn't matter what the instance collation is as long as the SharePoint databases use Latin1_General_CI_AS_KS_WS.


    Jason Warren
    Infrastructure Specialist
    Habañero Consulting Group
    www.habaneros.com/blog



    Wednesday, April 11, 2012 3:56 PM
  • Huh. Microsoft have changed their recommendation:

    We support any CI collation for the SQL instance (for master, tempdb databases). However we recommend using Latin1_General_CI_AS_KS_WS as the instance default collation (master, tempdb databases).

    ...

    Article ID: 2008668 - Last Review: August 29, 2012 - Revision: 6.0

    SQL_Latin1_General_CP1_CI_AS is only the default collation for servers in the "English (United States)" system locale anyway. Rest of the world defaults to the Latin1_General_CI_AS collation.

     
    • Proposed as answer by tom3411 Friday, October 05, 2012 11:50 AM
    Wednesday, September 26, 2012 11:10 PM
  • Suggests to me why they updated the wording. Good find.

    Jason Warren
    Infrastructure Architect

    Wednesday, September 26, 2012 11:13 PM
  • There are two different Levels of the collation:

    Collation of SharePoint Databases (like Config_DB, Content_DB, SA_DB ...):
    SharePoint always use Latin1_General_CI_AS_KS_WS as collation and it is not supported to Change this.

    Default Collation of the SharePoint Instance:
    This configuration you have to Change if you install the instance on the SQL box. If you know, you will have only SharePoint in the Instance, you can configure this with Latin1_General_CI_AS_KS_WS. The result of them is that all System databases (Master, tempDB ...) has also the SharePoint collation and we do not loose Performance in SQL internally. Unfortunatelly you cannot Change the Default collation later on.

    I always recommend to use Latin1_General_CI_AS_KS_WS if possible.

    Additional hint: the Article 2008668 is written for SharePoint Server 2007, not for 2010. But this configuration is also recommended for 2010 

    Friday, September 28, 2012 8:13 AM