Return
-
Title
SQL Server back end FMS collation question -
Description
Customer is asking questions on behalf of his DBA about the collation in SQL Server back end DB
FMS requires Case sensitive (NOTE DB was created from scripts and new FMS is up and running – they are willing to re-create Db if necessary)
DB was created as Case insensitiveThere are questions on what will happened when Foglight uses temp Db to create tables (I think we create all temp tables in our own schema –so not sure if we have to worry)
See the details below in the DBA Question
When installing a SQL Server instance, you choose the default collation for that instance. The system databases (master, model, msdb, tempdb) utilize this collation.
All other databases will use this collation unless specified otherwise; SQL Server allows for the collation to be set at the database level.Our SQL Server instances use the Microsoft default collation of SQL_Latin1_General_CP1_CI_AS. This collation is a case insensitive collation.
The Foglight SQL Server database(s) require a case sensitive collation (SQL_Latin1_General_CP1_CS_AS).
This inconsistency could be an issue if Foglight utilizes temporary tables, as they are created in the tempdb database.Below is an excerpt from an article that describes two possible solutions to the issue.
Assuming Foglight utilizes temporary tables, please follow up with the vendor to insure that the first solution is utilized by their application.Collations control the physical storage of character strings in SQL Server. According to Books Online, "a collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared." Separate collations can be specified down to the column level in a table, and each column can be assigned different collations."Unfortunately, if you are using temporary tables (or table parameters) in your application, and your database -
Resolution
Foglight is okay in this regard. Foglight does use temporary tables, but the majority of those tables only hold numeric data.
There appears to only be one case where a temporary table has a string column, and in that case the table is created so that the column is given the same collation as the Foglight database.
Our in-house SQL Server installations were created with the same default, case-insensitive collation and as far as I know, no problems have been encountered.