First server installation with standalone data warehouse database

This section covers scenario that deploys Management Server with standalone data warehouse database server.

When the data warehouse database is deployed on a separate server, all statistics related operations does not affect the performance of production database (expect for generating the statistics).

This section describes only the database configuration specific pages of the installer. For the description of the pages preceding and following the database configuration, see the First server installation section.

Use this deployment mode only when you know you will need this due to the extra performance it brings. There is some advanced configuration necessary to be done for this deployment mode to work.

Microsoft SQL database server

  1. On the Database configuration page select "External Microsoft SQL database server" option, check the "User external database for data warehouse" option and select "Different server" from the options below the checkbox. Then click Next.

    images/download/attachments/160481280/image2019-11-11_7-52-41.png

  2. On the Microsoft SQL database page for the production database, fill in the connection settings to the production database. Then click Next.

    images/download/attachments/160481280/MSSQL_configuration-production.png

    Production database must be accessible from the network

    It is advised to enter the Database server hostname/IP address as a non-loopback (e.g. localhost or 127.0.0.1) network address. The installer will convert such loopback network address to the Network interface chosen on a latter page in the installer, but entering the non-loopback address will immediately test if the connection to the database works from such network interface correctly.

  3. On the Microsoft SQL database page for the data warehouse database, fill in the connection settings to the data warehouse database. Then click Next.

    images/download/attachments/160481280/MSSQL_configuration-warehouse.png

    Database user permissions

    Connection between production database and standalone data warehouse database is implemented using T-SQL function sp_addlinkedserver. This function requires special privileges when installing the database. Please make sure the database user for the data warehouse database is having the sysadmin role assigned.

  4. Following pages are the same as in the First server installation section.

After the installation, follow this guide to allow the connection between both databases Configuring MS SQL for Server link.

PostgreSQL database server

  1. On the Database configuration page, you can select either "Embedded PostgreSQL 11 database server" or "External PostgreSQL 11 database server" option, check the "User external database for data warehouse" option and select "Different server" from the options below the checkbox. Then click Next.

    images/download/attachments/160481280/image2019-11-11_7-53-49.png

  2. Depending on what type of database (embedded or external) you chose in the previous step, on the you will either:

    1. On the Embedded database configuration page, enter the password of the "postgres" user of the embedded PostgreSQL database. Then click Next.
      images/download/attachments/160481280/PGSQL_configuration-embedded.png

      The database must be accessible from the network

      The database will be created and available on the port 5433. Please, make sure that the port is accessible from the network (i.e. from the server where the data warehouse database will be located). The installation will fail otherwise.

    2. On the PostgreSQL database page for the production database, fill in the connection settings to the production database. Then click Next.
      images/download/attachments/160481280/PGSQL_configuration-production.png

      Production database must be accessible from the network

      It is advised to enter the Database server hostname/IP address as a non-loopback (e.g. localhost or 127.0.0.1) network address. The installer will convert such loopback network address to the Network interface chosen on a latter page in the installer, but entering the non-loopback address will immediately test if the connection to the database works from such network interface correctly.

  3. On the PostgreSQL database page for the data warehouse database, fill in the connection settings to the data warehouse database. Then click Next.

    images/download/attachments/160481280/PGSQL_configuration-warehouse.png
  4. Following pages are the same as in the First server installation section.

After the installation, follow this guide to allow the connection between both databases Configuring PostgreSQL for remote database connection.