1. Creating the shared repository database on your database server

Applies to the following products: 
Questionmark Perception
Applies to the following Perception versions: 
Perception 5.4

As you are installing a new instance of Perception, you now need to create a database for your shared repository.

The shared repository database needs to be created on your database server.

The full version of SQL Server 2008 (or 2008 R2) or Oracle 11g must be used for the shared repository, Perception_MAIN, and Perception_RW databases.

Select the database system that you are using on your database server and follow the instructions:

SQL Server 2008


Before you create the shared repository database

In SQL Server, there are two database collations you need to set: the SQL Server collation and the Database collation.

For English and non-English characters to appear correctly when using SQL Server, your collations should be set as:

  • SQL Server collation must be configured to use the collation SQL_Latin1_General_Cp1_CI_AS. The collation utilizes the 1252 (ANSI - Latin 1) codepage. The collation SQL_Latin1_General_Cp1_CI_AS is the case-insensitive version. You should take care not to use the case-sensitive version of the collation, which can be identified with the initials CS appearing in the collation title, while the case-insensitive version can be identified by the initials CI.

    This must be applied to the instance of SQL Server when installing SQL Server. Please be aware that, when installing SQL Server 2008, it will automatically attempt to get the most appropriate collation depending on what the server locale has been set as. In most cases, this will need to be manually changed to use the SQL_Latin1_General_Cp1_CI_AS collation.

  • Database collation must be set as SQL_Latin1_General_Cp1_CI_AS to ensure that all data written in Perception is stored and displayed correctly. It will be set to this collation by Perception when it is created.

Please note that the collation used should be SQL_Latin1_General_Cp1_CI_AS, not Latin1_General_CI_AS.

Users wishing to set up an English language version of Perception on a non-English operating system and database should ensure that the correct language packs are installed for both the database and the operating system. Additionally, the language pack 1252 (ANSI - Latin 1)must be installed for both servers and the SQL Server collation and database collation set to SQL_Latin1_General_Cp1_CI_AS.

The question searching facility in Authoring Manager requires the full-text indexing component of SQL Server to be installed and configured and will not work without it. Once full-text indexing is installed, it will need to be enabled for the database before the repository is created.

Create a database and user for the shared repository

To create a SQL Server 2008 database for use with Perception:

  1. Open SQL Server Management Studio
  2. Right-click the Database folder on the attached database server and select New database
  3. Enter the database name. For example, Perception_REPO.
  4. From the Options page set the collation to SQL_Latin1_General_CP1_CI_AS and click OK

Once you have created your database, you need to set up a new login. To do so:

  1. Still in SQL Server Management Studio
  2. Expand the server folder for the server on which you have created your database in the navigation pane
  3. Expand the Security sub-folder
  4. Right-click Logins and select New login...
  5. Enter a name for the new login. For example, Perception_REPO.
  6. Select SQL Server Authentication and enter a password
  7. Uncheck the Enforce password expiration and User must change password at next login checkboxes
  8. In the Default database list, select the database you created above. For example, Perception_REPO.
  9. Open the User Mapping page and tick the database you created
  10. Enable the db_owner and public permission checkboxes
  11. Navigate to the Server Roles page
  12. Enable the bulkadmin and public server role checkboxes

  13. Click OK


Switch on full-text indexing for the shared repository database

If you intend to use question searching in Authoring Manager, you will need to ensure that full-text indexing is enabled for the shared repository database. To enable full-text indexing in SQL Server Management Studio:

  1. Right-click on the shared repository database (Perception_REPO), and select Properties
  2. Click Files from the Select a page menu
  3. Enable the Use Full-text indexing checkbox
  4. Click OK

If the checkbox is already ticked and grayed out, this is because full-text indexing has been enabled for the whole server, meaning all the databases have full-text indexing enabled by default.

Now that you have created a user and database for the shared repository, you will need to create an ODBC connection to it on your Perception server. For information about how to do this, please continue to step 2 using the link below.



Before you create the shared repository database

There are a number of factors that need to be taken in to account if using Oracle as your database server for Perception. These include:

  • For non-English characters (Latin characters and other Western European characters) to appear properly when using Oracle, your Oracle installation must be configured to utilize the AL32UTF8 character set
  • For 2-bit characters (such as Japanese or Chinese), you must utilize the WE8MSWIN125 database character set
  • If using the WE8MSWIN1252 database character set on a non-English operating system the National Character Set (NLS) must be set to English (either US or UK)

No other character sets can be reliably supported at this time. You can either change your database to use the appropriate character set (providing that other applications using Oracle can work with this), or else set up a separate Oracle installation with the appropriate character set for Perception to use.

To use the Scoring Tool component of Perception with Oracle, ODP.NET (Oracle Data Provider) 9.2 or higher must be installed on the server on which you plan on running Perception on. ODP.NET can be obtained from the Oracle website:


The question searching facility in Authoring Manager requires Oracle Text to be installed and configured, and it will not work without it. Oracle Text is not installed by default in some versions and may need to be installed.

Please note:

  • If you haven't already setup a destination file for your Oracle databases, please refer to the upcoming Set the Oracle database file destination section of this page.

Set the Oracle database file destination

Before creating any Oracle Tablespaces, it is recommended that you ensure the database file destination path is set for your Oracle instance, especially if you are working with a new installation of Oracle.. This will need to be done on your database server used in your Perception installation before creating any Tablespaces.

If you have previously set the destination paths for your databases previously, please skip the following section and continue with the install process as highlighted. For complete details about how you might setup the destination path, please refer to your Oracle documentation. For example:

Creating Oracle-Managed Files in the Oracle Database Administrator's Guide

To do this, you will need to run a SQL command via the SQLPLUS command prompt. To enter the SQLPLUS command line interface, enter the following at a command line prompt:

sqlplus system/<password>@<myserver>/<mysid>


<password> is the password associated with the SYSTEM user

<myserver> is the server name for your Oracle installation (i.e., your database server)

<mysid> is the System ID for your Oracle server set during installation

Once logged in, run the following command:


...where <path> is the location that you would like the database (dbf) files to be located. For example, if your Oracle environment is on a:

  • Linux/Unix environment, the <path> may be something like: /u01/app/oracle/oradata
  • Windows environment, the <path> may be something like: C:\app\<username>\<oracle version>\oradata\<instance name>

    ...especially if you are working with a new installation of Oracle.

Create a Tablespace and User

For Oracle, you will need to create a Schema for use by the shared repository. This involves creating a Tablespace and User. The following section provides details about doing this.

After ensuring that the database file destination path is set for your Oracle instance, to create a Tablespace for your database:

  1. Open Enterprise Manager Console on your database server
  2. Login to the database server you will be creating the new database on
  3. Open the Server tab
  4. Under Storage, click Tablespaces
  5. Click Create
  6. Enter a name for the new Tablespace. For example, Perception_REPO.
  7. Under Datafiles, click Add

  8. Enter the following information:
    • Enter a File Name for the datafile
    • Enter the location of an available File Directory (this file location must exist before this section will complete accurately)
    • Set the File Size to a larger number than the default 100MB for high-use systems
    • Tick Automatically extend datafile when full (AUTOEXTEND) and specify a suitably high value in the Increment box
  9. Click Continue to return to the Create Tablespace page, and then click OK to create the Tablespace. The new table will appear in the list of Tablespaces.

To create a database user:

  1. Open the Enterprise Manager Console
  2. Login to the database server you will be creating the new database on
  3. Open the Server tab
  4. Under Security, click Users
  5. Click Create

  6. Enter the following information:
    • Enter a database user name and password
    • In the Default Tablespace box, enter the name of the Tablespace you created above
    • In the Temporary Tablespace box, enter the name of a temporary Tablespace. By default, this is TEMP.
  7. Open the Roles tab, and click Edit List

  8. Assign the user the following roles:
    • CTXAPP (only avaiable if OracleText is available)
  9. Click OK
  10. Open the System Privileges tab, and click Edit List

  11. Select the following System Privileges, and click OK:
  12. Click OK to create the user

Next, you will need to configure the Local Service Name and then create an ODBC data source.

Setting up a Local Service Name

A Local Service Name is a way of defining a connection to a Tablespace. To configure the Local Service Name, you will need the Oracle client installed on the same server on which you intend to install Perception (NOT your database server).

To configure the Local Service Name:

  1. Open Oracle Net Manager on the same server on which you intend to install Perception (NOT your database server)
  2. Select the Service Naming folder in Oracle Net Configuration | Local

  3. Click the Create (+) button. The Net Service Name Wizard screen is displayed.
  4. Enter a Net Service Name. For example, Perception_REPO (make a note of this name, as you will need to use it when you create an ODBC data source).
  5. Click Next

  6. Select TCP/IP (Internet Protocol) and click Next
  7. Enter the Host Name and click Next. This is the name of the server that the Oracle database is installed on (i.e., your database server). If you are not using the default Port Number, you can enter the port you are using as well.

  8. Enter the Service Name of the Oracle instance and click Next
  9. Click Test to verify the connection
  10. Click Finish. Save the changes when you close Oracle Net Manager.

Now that you have created a user, Tablespace, and setup the Local Service Name for the repository, you will need to create an ODBC connection to it on your Perception server. For information about how to do this, please continue to step 2 using the link below.