Collation settings for K2?

Though this is an old question, I guess it is imortant one to answer. In fact K2 blackpearl database has very specific collation requirements, namely:

Case sensitive databases are NOT supported.

The following collation setting is required: Latin1_General_CI_AS

Please do not fail to notice and understand this REQUIRED word. Use of any other collation puts you into unsupported state from K2 support point of view, and trust me conversion of K2 database into new collation is not an easy task.

The only issue with this requirement is that it doesn't emphasized enough throughout product documentation and stated only here:

http://help.k2.com/onlinehelp/k2blackpearl/icg/current/webframe.html#microsoft_sql_server_2008__reporting_services.html

Though it is not mentioned in above mentioned documentation K2 requires this collation not only on database, but also on SQL server instance level as K2 makes extensive use of temp tables in the tempdb – which inherits its collation from the masterdb – which inherits its collation from the SQL Instance. This requirement not stated in documentation yet, but there is documentation update request for this already.

Next, if you read all this and take it seriously you may notice that in fact there is no such SQL collation as "Latin1_General_CI_AS", among SQL collations (those prefixed with "SQL_"), and in fact you have to choice Windows collation for SQL server with name "Latin1_General_CI_AS".  "SQL_" collations use SQL's own proprietary code pages, Windows  collations based on the Windows OS code pages. Windows keeps its collations up to date more often, and compatibility is better for the client applications. Hence the best practice is to use Windows collations in general, and we use one of such for K2 database.

Key thing here is to be aware about this requirement before your production roll out, as you have to remember that by default SQL server installation picks up collation based on Windows Server localization version/settings, and K2 Setup Manager as for now  (4.6.9) will create K2 database with collation you have on your SQL server instance (even if it is unsupported one).

I think it is worth sharing here that collation requirement for K2 DB SQL server instance is now stated in K2 blackpearl compatibiltiy matrix (for "lazy readers" it is note 3 in SQL section). I also wrote little blog post about this _. And for those who are more on visual side of perception: