Project Server (SharePoint) - Database Collation question. RRS feed

  • Question

  • Hi,

    I have been looking at the performance of our SharePoint database server and one of its uses is Project Server.

    Our databases are set as follows

    Project Server Databases (Latin1_General_CI_AS)

    SharePoint Server Content Databases (Latin1_General_CI_AS_KS_WS)

    TempDB (Latin1_General_CI_AS)

    It seems that the Project Server databases should be (SQL_Latin1General_CP1_CI_AS):



    Database Collation

    Project Server Archive


    Project Server Draft


    Project Server Published


    Project Server Reporting


    SharePoint Server Content


    I may be worrying unnecessarily but my understanding was that the tempdb should be the same collation as the user databases (where possible). With the SharePoint content and the Project Server databases on the same instance this is not possible.

    Should I...

    1. Not worry about it (the performance impact is negligable).

    2. Create seperate instances so that the TempDB can match the user databases (I am worried that the overhead of an extra instance will be more than the overhead of different collations).

    3. Implement Contained/Partially contained databases (new possible option)

    4. Something else?

    Also how bad is it that the Project server databases are Latin1_General_CI_AS rather than SQL_Latin1General_CP1_CI_AS.

    Thanks in advance


    • Edited by Shaun Brewer Friday, February 6, 2015 9:16 AM Clarification
    Tuesday, February 3, 2015 3:07 PM

All replies

  • Anyone?

    I would be very greatful for any advise on this subject.

    Thanks again


    Wednesday, February 4, 2015 10:06 AM
  • Shaun,

    Microsoft Recommends that the TempDBs be in Latin1_General_CI_AS. matching the SharePoint DBs.


    Also, Project Server Databases are not wholly "SharePoint Databases". As such, I think you will have issues if you change the recommended collation.

    I have always created TempDBs to match with SharePointDB collation, and have never had issues.

    So, in summary, I think you are OK. 

    Other on this forum might have more input.


    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Wednesday, February 4, 2015 10:51 AM
  • Hi Prasanna,

    thank you for taking the time to respond.

    From the link - I read this as Microsoft insisting that you use Latin1_General_CI_AS_KS_WS for the SharePoint content databases and recommending it for the system databases including TempDB?

    Our TempDB is not the same collation as the SharePoint content databases but the same as the Project Server databases. Which brings me back to my original questions.

    From the Technet article:

    The following TechNet Article talks about the SQL Server collation for the SharePoint databases

    “The SQL Server collation must be configured for case-insensitive. The SQL Server database collation must be configured for case-insensitive, accent-sensitive, Kana-sensitive, and width-sensitive. This is to ensure file name uniqueness consistent with the Windows operating system.”

    However, we do not support changing the default collation (Latin1_General_CI_AS_KS_WS) for SharePoint databases to any other collations (CI, AS, KS, WS).

    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).

    • Edited by Shaun Brewer Wednesday, February 4, 2015 11:33 AM
    Wednesday, February 4, 2015 11:32 AM
  • I have been reading up on Contained/Partially contained databases


    Could the be a solution, I would love to have input from someone who has tried this.

    Thanks again


    Wednesday, February 4, 2015 12:03 PM
  • Shaun,

    Sorry, I misread your question. However, I have never used a different collation before than the recommended on the Project Server Databases, so I am not sure if there are any ill effects of using it under a different collation.

    Are you actually experiencing any issues? May be you could test by back-up and restoring into a different farm (or even a VM).

    Sorry I cannot be of more help on this matter.


    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Wednesday, February 4, 2015 12:10 PM
  • Hi again Prasanna,

    Our SharePoint perfomance is not great and the server load was peaking, waiting tasks increasing etc. I have resolved a few 'big' issues however during the investigation I spotted the Collation issue and wanted to understand if this is something I should worry about.

    I am new to the company and was not involved in the set-up. I am also not a SharePoint / Project Server expert and my SQL Server skills were a little rust although this is changing rapidly...

    A test environment would be nice!?! Been on the list since before I joined...


    Wednesday, February 4, 2015 12:58 PM
  • If anyone can answer this I would be most greatful.

    Thanks again


    P.S. I have updated the 1st post with a potential additional solution.

    Friday, February 6, 2015 9:14 AM
  • This is a very academic question and very had to provide a good answer.  The performance question would best be ask to the SQL Server forums, because the SQL experts would be more experience in the effect of collation.

    My only suggestion for a best practice for performance is to increase the number of tempdb to match the number of processors.


    Michael Wharton, MVP, MBA, PMP, MCT, MCTS, MCSD, MCSE+I, MCDBA
    Website http://www.WhartonComputer.com
    Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Monday, February 9, 2015 3:51 AM