Regional Settings

You can use our products with the database to use pretty much any of the standard SQL Server collations, character sets or case options. We have ensured that all system columns that rely on specific sorting are forced to use collation Latin1_General_BIN. The installer also uses upper case characters for all objects so you don't need to worry about case sensitivity vs insensitivity.

However here are the settings we typically recommend:

  • Set the default collation to SQL_Latin1_General_CP1_CI_AS
  • Ensure that you have SQL Server running in Case Insensitive mode
  • We use NCHAR, NVARCHAR string types so all data is unicode, so no character set is specified

Other Settings

To improve performance and simplify maintenance we recommend these settings:

  • Auto Close - unless you have a reason, ensure that this is set to 'False'
  • Auto Shrink - unless you have a reason, ensure that this is set to 'False'
  • Database Transaction Log - Unless you have a dedicated SQL Server DBA, we recommend using the "Simple" recovery model and then do a scheduled full backup of the database every evening into the SQL Server .BAK file format. Not only does this make sure you have a reliable backup in place, it will also make sure that your transaction does not grow uncontrollably, which will degrade performance.
  • Antivirus Software - Make sure that your antivirus software on the database server excludes the folders in use by the system. For database servers that would be the location of the Spira/KronoDesk MDF/LDF files as well as the master MDF/LDF files.
  • Database Indexes - Fragmented database indexes tend to be the most common cause of performance issues. We recommend setting up a weekly SQL Server job to run the one of the SQLs found in this KB article.
  • Database File Fragmentation - Sometimes the MDF/LDF databases files get fragmented which can hinder performance, especially if the database has recently grown in size or the system was recently upgraded from one version of Spira to another:
    • Check the hard drive that the MDF/LDF files are stored on and make sure that the drive is healthy and in the case of RAID arrays, make sure that there are no failures reported by the RAID controller.
    • Size the MDF file to be at least 20% larger than the current size so that it has room to grow. Make sure that the MDF has an Auto-Grow size set to at least 20% of the current database MDF file size.
    • Size the LDF to be at least 20% of the MDF and ideally pre-size it to be twice the largest size it has ever previously been. Also set the Auto-Grow to be at least 20% of its current size.
    • Detach the database and perform a file-level defragmentation of the MDF and LDF files (e.g. use the SYSINTERNALS contig.exe application). Then reattach the database.
  • Finally, check the general Spira and KronoDesk performance guidelines.