1. Perception 5.7 Install Guide
  2. Advanced information
  3. Create the databases manually

Create the databases manually

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

Perception 5.7 requires several databases, as depicted in the image below:

As you can see, two databases will need to be created on your database server. These are your Main (Perception_Main) and Results Warehouse (Perception_RW) databases (this section is not concerned with the creation of the shared repository, which is outlined elsewhere in this guide). A third database is also required to be installed on your ETL server, where the ETL service is installed. This third database, the temporary ETL (ETL_Temp) database, is used by the ETL service to ensure the data from the repository is correctly formatted for the Results Warehouse database.

If you want to create these three databases automatically, please refer to step 10 (Create the Main and RW databases) and step 11 (Create the temporary ETL database) of this installation guide.

To manually create your databases and users, you will need to

  • create the necessary database logins
  • create the necessary databases and
  • create a relevant ODBC connection to the database from your Perception server.

Refer to the sections below to view instructions on creating these three databases (Perception_Main, Perception_RW, and ETL_Temp).

SQL Server

In the steps below, the following example names are used to make the instructions easier to follow.

For:

Example login name

Example database name

Configuration and People database

Perception_Login

Perception_Main

Results Warehouse database

Perception_RW

Temporary ETL database

Perception_ETL_Temp

The steps below are intended for the creation of the new databases. They do not include the creation of the shared repository database. If you are installing a fresh installation of Perception, you will first have to create it. For further information, refer to step 7 (Create the shared repository database on your database server).

Also, if you don't use the default names, please ensure you do not use illegal characters when creating your database. For more information, please refer to Illegal characters.

To create the necessary databases manually:

Configure SQL Server authentication

To ensure that SQL Server is configured to provide authentication based on the SQL Server login, you will:

  1. Open the SQL Server Management Studio
  2. Right-click on the database server, and click Properties
  3. Open the Security page
  4. Ensure that the SQL Server and Windows Authentication Mode option button is selected and click OK

Create a database login

You will need to create a login for your databases to use. You will need to use the same login name for all the databases.

To do so:

  1. Open SQL Server Management Studio and login with your administrator details.
  2. Expand the server folder 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 (e.g., Perception_Login).
  6. Select SQL Server Authentication and enter a password.
  7. Disable the Enforce password expiration and User must change password at next login checkboxes.
  8. Select Server Roles from the Select a page navigation menu on the left.
  9. Enable the bulkadmin checkbox.
  10. Click OK to create the login.

Create the Perception_Main and Perception_RW databases and assign them the login

You now need to create the Main and Results Warehouse databases.

To do this:

  1. Open SQL Server Management Studio and login with your administrator details.
  2. Expand the server folder in the navigation pane.
  3. Right-click Database and select New Database....
  4. Enter a name for the database in the Database name textbox.

    For example: Perception_Main if you are creating the Configuration/People database, or Perception_RW if you are creating the Results Warehouse database.

  5. Click the Browse (...) button next to the Owner textbox.
  6. The Select Database Owner screen displays. Click Browse....
  7. Locate the user you created above and enable the checkbox next to it.
  8. Click OK.
  9. Select Options from the Select a page navigation menu of the left.
  10. In the Collation drop-down box, select SQL_Latin1_General_Cp1_CI_AS.
  11. Click OK to create the database.

Create the temporary ETL_Temp login and database

In this basic installation, the temporary ETL database (ETL_Temp) needs to be created on your ETL server.

First, you need to create a database login. To do so:

To do so:

  1. Open SQL Server Management Studio and login with your administrator details.
  2. Expand the server folder 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, e.g., Perception_Login. This should match the user you created for your SQL Server databases.
  6. Select SQL Server Authentication and enter a password.
  7. Disable the Enforce password expiration and User must change password at next login checkboxes.
  8. Select Server Roles from the Select a page navigation menu on the left.
  9. Enable the bulkadmin checkbox.
  10. Click OK to create the login.

Now, you need to create the actual ETL_Temp database. To do so:

  1. Open SQL Server Management Studio and login with your administrator details.
  2. Expand the server folder in the navigation pane.
  3. Right-click Database, and select New Database....
  4. Enter a name for the database in the Database name textbox (e.g., Perception_ETL_Temp).
  5. Click the Browse (...) button next to the Owner textbox.
  6. The Select Database Owner screen displays. Click Browse....
  7. Locate the user you created above and enable the checkbox next to it.
  8. Click OK.
  9. Select Options from the Select a page navigation menu of the left.
  10. In the Collation drop-down box, select SQL_Latin1_General_Cp1_CI_AS.
  11. Click OK to create the database.

You have now created the ETL login and database.