A2. Manually link the databases

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

You will need to link your databases to make sure that each one can view and run procedures from the other. If you have chosen not to let the configuration application link the databases for you automatically, you will need to do it manually. The links that must be created between the databases are as follows:

  • Results Warehouse must be linked to ETL_Temp
  • ETL_Temp must be linked to Repository
  • Perception_Main must be linked to Result Warehouse

To manually link your databases, please refer to the relevant section:

SQL Server 2008
  1. Using SQL Server Management Studio, connect to the server (as a database administrator) you are linking from and add a link to the server you are linking to. For example, connect to the database server hosting the Results Warehouse database (in this case, your database server) and link it to the machine hosting the ETL_Temp database (in this case, your Perception server).
  2. Right-click on the server name and select New Query. Enter the code below in the query window and execute it:

    EXEC sp_addlinkedserver <db_server_name_to_link_to>;

    ...where <db_server_name_to_link_to> is the database server you are linking to. In the above example, this would be your Perception server.

  3. Repeat the above process to create the others links, i.e., ETL_Temp to Repository and Perception_Main to Result Warehouse

Ensure that protocols are enabled for the SQL Server instance on both servers (in SQL Configuration Manager) to allow communication between them (e.g., TCP/IP, Named Pipes).

If your Repository and Results Warehouse share a database server, you will still need to make a link to and from it to the ETL_Temp database server installed on your Perception server machine.

Oracle 11g
  1. Open a command prompt
  2. Use SQLPLUS to connect to the server hosting the Results Warehouse by using the details you entering when you were manually creating the Oracle databases
  3. Enter the following code and execute it:

    CREATE DATABASE LINK StagingToDataWarehouse CONNECT TO <Perception_ETL_Temp> IDENTIFIED BY <Perception_ETL_Temp_Password> USING '<Perception_ETL_Temp_Server_Name>/<Perception_ETL_Temp_SID>';

  4. Next, execute the following code:

    CREATE DATABASE LINK <Perception_MAIN> CONNECT TO <Perception_Main -user> IDENTIFIED BY <Perception_Main -password> USING '<Server Name>/<SID>';

  5. Now connect to the ETL_Temp database as setup in A1. Create the databases manually by using the relevant SQLPLUS login method
  6. Enter the following code and execute it:

    CREATE DATABASE LINK StagingToPerception CONNECT TO <Perception_Repo - user> IDENTIFIED BY <Perception_REPO -password> USING '<Server Name>/<SID>';