none
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):

    https://technet.microsoft.com/en-us/library/ff800813(v=office.14).aspx

    e.g.

    Database Collation

    Project Server Archive

    SQL_Latin1_General_CP1_CI_AS

    Project Server Draft

    SQL_Latin1_General_CP1_CI_AS

    Project Server Published

    SQL_Latin1_General_CP1_CI_AS

    Project Server Reporting

    SQL_Latin1_General_CP1_CI_AS

    SharePoint Server Content

    Latin1_General_CI_AS_KS_WS

    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

    Shaun




    • 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

    Shaun

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

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

     https://support.microsoft.com/kb/2008668?wa=wsignin1.0

    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.



    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Wednesday, February 4, 2015 10:51 AM
    Moderator
  • 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
    http://technet.microsoft.com/en-us/library/cc288970.aspx#section1

    “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

    https://msdn.microsoft.com/en-us/library/ff929071(v=sql.110).aspx

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

    Thanks again

    Shaun

    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.


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Wednesday, February 4, 2015 12:10 PM
    Moderator
  • 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...

    Shaun

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

    Thanks again

    Shaun

    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.

    Cheers1


    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
    Moderator