7. Creating the shared repository database on your database server | Questionmark

7. Creating the shared repository database on your database server

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

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 must be used for the shared repository, Perception_MAIN, and Perception_RW databases.

SQL Server

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, 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 database for use with Perception:

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

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 subfolder.
  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 options.
  8. In the Default database list, select the database you created above. (in this case, Perception_REPO).
  9. Open the User Mapping page and tick the database you created.
  10. Enable the db_owner and public permission options.
  11. Navigate to the Server Roles page.
  12. Enable the bulkadmin and public server role options.
  13. Click OK to create the login.

After the user has been created, check that all required permissions have been assigned.

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 option.
  4. Click OK.

If the option 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 8 using the link below.