We’ve developed some resources to help you work effectively from home during COVID-19 Click to learn more

Prepare Database

In this article

    The central database may be running on either Microsoft SQL Server or Oracle. Please refer to the manufacturers webpages for more information.

    Tested databases

    Databases supported by SuperOffice CRM 8

    Our experience tells us that other configuration also should work but SuperOffice will not guarantee for any other configurations than the ones listed below. Be especially aware of the different versions of the ODBC drivers. This has turned out to be greatest source of incompatibility.

    For database specific system requirments please check the vendor Microsoft or Oracle.

    Database 8.0 /
    8.0 SR1
    8.0 SR2
    - SR6
    8.1 8.2 8.3 8.4 8.5
    MS SQL Server 2019
    MS SQL Server 2017 *
    MS SQL Server 2016
    MS SQL Server 2014
    MS SQL Server 2012 SP1 and SP2
    MS SQL Server 2008 R2***
    Oracle 18c **
    Oracle 12c
    Oracle 11g

     * - Compatiblity report

     ** See below Oracle 18c compatibility info

     *** Microsoft has finished Extended support for this product

    Create Oracle database

    Are you going to run SuperOffice on an Oracle database, follow these steps to set up the target database.

    Oracle - Setting up the server

    In this document we will not give a description on how to install the Oracle server. Please refer to the documentation provided by Oracle. We do however have some comments on the server installation.

    If your Oracle server is going to serve other databases than the SuperOffice database, you might want to create a own Oracle instance (SID) for the SuperOffice database, or it will be enough to just place the SuperOffice data into a separate tablespace. What you decide on depends on how your server is going to be maintained, and we don't have any preferences one way or another. We do recommend that you at least create a separate tablespace for the SuperOffice data.

    There are some parameters that you only can set during the installation of the server (or creation of the SID), and these should be considered changed during the installation of your Oracle server. The best examples of this is:

    • National Language Support
    • Code page or Character sets
    • Database Block size

    Preparing the Oracle database

    Because customers are sure to have database policies and opinions that we don’t know about, installation on an Oracle server should be done by the customers’ resident DBA. However, if a SuperOffice consultant does do the installation, it is recommended to use the Database Configuration Assistant (DCA).
    In the DCA, choose the “Custom Database” template to avoid installing numerous options and sample data. Then specify passwords, file storage, and so forth. Turn off Oracle Text, OLAP, Spatial, Data Mining, Ultra Search, and other unused things, unless you specifically need them. These options take up disk space, ram, CPU, and can decrease overall database performance.
    In step 10, 4 tabs, you get to the interesting parts. As for Memory and Sizing, set those to what you think is best.  For character sets, Oracle has two character set concepts: database character set and national character set. When you need to set up Unicode on Oracle, please read this first. The default is Code page 1252 ISO-Latin.
    To support Unicode, select the “Use Unicode (AL32UTF8)” option. Choose “AL16UTF8” as the National character set. If you choose UTF8, you may run into problems when storing long Chinese texts in appointment descriptions. Please also take note how the representation surfaces. VARCHAR is limited to 4000 bytes, which is 2000 characters in UTF16, or anywhere from 4000 to 1300  in UTF8. You could choose the “Custom” option for database character set, but you risk being told (later on) that your choice was illegal.

    Creating the SuperOffice tablespace

    Create a tablespace with a descriptive name etc. SuperOffice_DATA and create a datafile called SUPEROFF.ORA (or some other descriptive name). The size should be 200 Mb, and we recommend that you turn on the autoextend options.

    Creating the CRM7 user*

    You now have to create the user who is going to be the owner of all tables used by SuperOffice. If you delete this user after SuperOffice is installed, all SuperOffice data will be deleted.

    Set the following values on the General-Tab:

    Parameter

    Value

    Name

    CRM7 *

    Profile

    Default

    Password

    YourSecurePassword*

    Default tablespace

    SuperOffice_DATA

    Temporary tablespace

    TEMPORARY_DATA

    Status

    Unlocked

    * Note - you may call the CRM7 user anything you want, this is just an example. Also, you may no longer use CRM7myd or any variation of this as a password for the system user.

    Move to the Roles/Privileges-Tab and grant the following roles:

    CONNECT with Admin Option

    DBA with Admin Option

    Set Open Cursors

    The maximum number of Open Cursors depends on the memory available.
    In order to run CRM 7, Open Cursors must be at least 1000.

    To set the number of open cursors, do the following:


    OPEN_CURSORS = 1000

     

    Tuning Parameters

    There are many opinions on how to tune your Oracle server to perform optimal. Our opinion is that in general the more memory installed and allocated to your Oracle server, the better it will perform.

    In addition we will here list a set of server parameters that we mean are optimal for a normal SuperOffice installation:

    Parameter

    Value

    Open_cursors

    1000

    Shared_pool_size

    9000000

    Db_block_size*

    4096

    Db_block_buffer

    200 * Db_block_size

    Log_buffer

    32768

    Dml_locks

    250

    Sort_area_size

    65536

    Processes

    150

    Db_file_multiblock_readcount

    16

     

    * can only be set when the database is created

    These parameters can be set using the Oracle Enterprise Manager Console

    Table spaces

    Temporary tablespace is hard coded to be : TEMPORARY_DATA
    Default tablespace is not hard coded. Give the name you want.

    Make sure the CRM7 user has this as "Default tablespace".

    All Oracle users created by SuperOffice will have Default and Temporary tablespace set to SYSTEM. The fact that Temporary tablespace is set to system for a huge amount of users, may result in that the Tablespace SYSTEM will eventually go full. To avoid this please update the tablespace settings for each Oracle-user that is created, using the Storage Manager.

    A tablespace with this name has to exist in order to run SuperOffice.
    3. Save and close the file
    4. Restart "Oracle Service" and "Oracle Start" in order to initialise the new settings.
    5. To check the new settings start "Instance Manager" and look into "Initialisation Parameters". 

    All Oracle users created by SuperOffice will have Default and Temporary tablespace set to SYSTEM. The fact that Temporary tablespace is set to system for a huge amount of users, may result in that the Tablespace SYSTEM will eventually go full. To avoid this please update the tablespace settings for each Oracle-user that is created, using the Storage Manager.

    When configuring an Oracle database using DbSetup/ServerSetup:

    The configuration program reads Default and Temp TableSpace names from SUPEROFFICE.INI

    [Oracle]
    DefaultTableSpace=USERS
    TemporaryTableSpace=TEMP

    This is useful if you have a non-standard Oracle database server.

    Users created by the setup program (and the web maintenance client later on) will use these settings to define the Oracle tablespace names that the users are granted access to.

    Create Microsoft SQL Server database

    Are you going to run SuperOffice on a Microsoft SQL Server, follow these steps to set up the target database.

    1. Create a new target database using the SQL Management tool

    2. Add a user (Security -> Users). The user may be called CRM8, but you are free to name the user whatever you like.

    3. Give the user db_owner permissions (lower list) on the new database.

    4. Create a new schema, the name of the schema will be the table prefix

    5. Create a new 32 bit ODBC System Data Source pointing to the new database.

    If you want to take advantage of the SQL Server full-text search you need to set this up before you run DBSetup/ServerSetup to create the new SuperOffice CRM database.

    SQL server native full text search

    Full text indexing is a database capability, and needs to be supported and enabled in the database. Contact your database vendor for information on how to enable this on your database.

    Service (formerly known as eJournal) has had this functionallity available since version 4.7. From CRM 7 this functionallity is now also available on some fields in Sales & Marketing win and web client.

    If full text search is enabled on the database, it will use special search syntax when searching for the following entities in Customer Service:

    • Request content (title and message text)
    • Knowledge base FAQ articles
    • Quick search field, when searching request titles

     

    And in Sales & Marketing:

    • All long text fields like appointment text, yellow notes, descriptions. Note: This is not a replacement of our fulltext search, since it only works on specific fields, as part of the ”structured” search system (Find)

    The new full text search functionality works in a different way than ordinary (much slower) text search, and there are some differences in search behavior. Using the classic text search the database would match against any record that contains your text query anywhere. With the new full text search capability, the text is portioned into words, and the words need to match – a part of a word will not match.

    This table illustrates the different search behavior when searching for “enigma”:

    Words in the database

    Match classic text search 

    Match new full text search

    Enigma Yes Yes
    Enigmatic Yes No
    Unenigmatic Yes No

    The speed of the search is highly dependent on the kind of search functionality you use.  In a database with a lot of requests and messages, a request content search can take several minutes to complete using the classic search functionality. With the new full text search functionality enabled it usually takes less than a second to complete such a query.

    To get full text search capability, you need to enable the Microsoft Indexing Service on the MSSQL server.  If it’s not installed, you need to install it.

    Table

    Fields

    message search_title, body, html_body
    ticket  title
    kb_entry title, question, answer, keywords
    text text

     

    Turn on full text search on existing database

    Dbsetup will detect if full text search is set up or not. If you have turned on the full text search on an existing database, you have to do a rebuild of the database to get the functionallity working.