Manually link the databases
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:
- The Results Warehouse must be linked to the ETL_Temp database.
- The ETL_Temp database must be linked to the shared repository.
- The Perception_Main database must be linked to the Results Warehouse.
To manually link your databases, please refer to the relevant section:
- 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).
- 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.
- 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.