Replicating the Application Database

This information is relevant for versions of Sisense V6.7 and later. For earlier versions, click here.

When you install Sisense, an application database is installed locally on your machine that contains metadata for ElastiCubes, dashboards, and users. If you have several application databases across multiple servers, you can combine them to form a replica set. The goal of a replica set is to ensure that each application database has the same metadata about ElastiCubes, dashboards, and filters. If one database fails on one server, the next available database can replace it. To maintain consistency throughout your configuration and support high availability, each database must be identical with the other databases in your configuration.

There are two types of nodes within a replica set. The primary node, which supports write operations and the secondary nodes, which replicate the primary database’s operations log and syncs the data to reflect that of the primary. The primary and secondary nodes can be hosted on the build or query nodes in a high availability configuration.

There are four steps to support high availability in Sisense:

  1. Setting Up Replica Nodes: Each database must be configured to connect to other instances in the replica set and share a KeyFile that is used for authentication.
  2. Creating a Replica Set: After setting up your nodes, you need to set one as the primary, which has write access to the other nodes in the replica set.
  3. Connecting to the Replica Set: Once you have created the replica set, now it’s time to connect it to Sisense.
  4. Connecting Additional Web Servers to Your Replica Set: Your replica set can support your Sisense Web Application. To do so, you need to connect it to the replica set instead of the localhost that it is connected to be default. This way, if your server’s database fails, the Sisense Web Application is supported by another database within the replica set.

This page describes how to set up your databases in a replica set and connect it to Sisense.

Prerequisites

  1. Servers with port 27018 open.
  2. RoboMongo installed on a machine that has access to those servers on port 27018 (any node can be on one of the servers).
  3. MongoDB installed on the machine. By default, this is installed when Sisense is installed. If Sisense is not installed, download the MongoDB version 3.4.5 zip file on the secondary servers from here.

Setting Up Replica Nodes

Note: For each machine that you have installed Sisense, you must stop the Sisense-related services from running to prepare the primary database that will be replicated to the other secondary nodes.

This procedure describes how to set up a replica node.

Each node in your replica set must have a MongoDB configuration file. The configuration file contains information about the database such as its open port and the name of the replica set the application database belongs to. When Sisense is installed on the node, this configuration file is created for you. For nodes in which Sisense is not installed, you must create this configuration file as described in the procedure below. In addition, each database must share a KeyFile used for authentication. This KeyFile must be the same on each instance, whether Sisense is installed or not.

To set up a replica node:

  1. Create a KeyFile. See Creating KeyFiles for more information.
  2. Create a MongoDB user with "write access". See Accessing the Application Database for more information.
  3. When Sisense is installed:
    1. Stop the Sisense.Repository service.
    2. Copy the KeyFile you prepared above as described in Step 1 and add it to the directory …\Program Files\Sisense\Infra\MongoDB.
    3. In the directory, …\Program Files\Sisense\Infra\MongoDB\mongodbconfig.conf, backup the MongoDB configuration and save the backup copy in another directory.
    4. In the MongoDB directory, open the mongodbconfig.conf file and uncomment the commented lines by removing # from the beginning of the row.
      storage:
      dbPath: “C:/ProgramData/Sisense/PrismWeb/Repository/DB”
      journal:
      enabled: true
      systemLog:
      destination: file
      path: “C:/ProgramData/Sisense/PrismWeb/Repository/Logs/sisenseRepository.log”
      net:
      bindIp: 127.0.0.1
      port: 27018
      # ssl:
      # mode: requireSSL
      # PEMKeyFile: keyFile path
      # PEMKeyPassword: keyFile password
      security:
      authorization: “enabled”
      #keyFile: “C:/Program Files/Sisense/Infra/MongoDB/keyfile”
      # replication:
      # replSetName: replica set name
    5. Bind each of your servers in your high availability configuration together by adding their IP addresses and separating them with a comma:
      For Sisense V6.7 and earlier:
      net:
      bindIp: [127.0.0.1, XX.XX.XX.XX, XX.XX.XX.XX]
      For Sisense V6.7.1 and later
      net:
      bindIp: 127.0.0.1,XX.XX.XX.XX
      Note: The value of bindIp has been changed from an array to a string. As a string, the IP addresses should be separated by a comma with no space in between. In addition, the only IP addresses to be included in the string are 127.0.0.1 and the public IP of your local server.
    6. Under security, define the location of your KeyFile created in Step 1.
      security:
      authorization: “enabled”
      keyFile: “C:/Program Files/Sisense/Infra/MongoDB/keyfile”
    7. Set a name for the replica set.
      replication:
      replSetName: replicasetname
      Save and close the mongodbconfig.conf file.
    8. Start the Sisense.Repository service.
  4. When Sisense is not installed:
    1. Copy the KeyFile you prepared above as described in Step 1 to your MongoDb’s configuration file folder.
    2. In the MongoDB configuration folder, open the mongodbconfig.conf file.
    3. Bind each of your servers in your high availability configuration together by adding their IP addresses and separating them with a comma:
      net:
      bindIp: [127.0.0.1, XX.XX.XX.XX, XX.XX.XX.XX]
      Note: You can add your PC’s IP address as well to allow you connect to the MongoDB with an application such as RoboMongo.
    4. Under security, define the location of your KeyFile created in Step 1.
      security:
      authorization: “enabled”
      keyFile: “C:/MongoDB/keyfile”
    5. Set a name for the replica set.
      replication:
      replSetName: <replicasetname>
    6. Save and close the mongodbconfig.conf file.

Creating a Replica Set

After you have prepared the MongoDB configuration file for each of your nodes, you must create a replica set with these nodes. With a replica set, each member has the same Sisense data but is otherwise independent. If the primary becomes unavailable, the replica set holds an election to select a new primary from one of the secondary nodes.

You should perform this procedure on your primary node. It sets the server as the primary node, and connects the secondary nodes to it.

To create replica set:

  1. Open Robomongo with a WriteUser account as described in Accessing the Sisense Application Database and select Open Shell to connect to the primary node.
  2. In the new shell, run the command rs.initiate() to create a replica set.
  3. Run the following commands:
    cfg = rs.conf()
    cfg.members[0].host = "XX.XX.XX.XX:27018" (Enter your IP address of the primary node and the MongoDB port, by default 27018).
    rs.reconfig(cfg)
  4. Run the command rs.add(“XX.XX.XX.XX:27018”), where the XX is the IP address of your secondary node. Run this command to add the unique IP address for each secondary node. For example:
    rs.initiate()
    cfg = rs.conf()
    cfg.members[0].host = "10.50.1.199:27018"
    rs.reconfig(cfg)
    rs.add("10.20.1.196:27018")
    rs.add("10.20.1.197:27018")
  5. Run the command rs.status() to verify that the replica set was configured properly. Run this command every couple of minutes to check the progress until stateStr of the secondaries service displays SECONDARY.

Connecting Sisense to the Replica Set

After you have created the replica set, you must connect the primary web application server to your replica set to support your high availability configuration.

To connect your replica set with your primary web server, you need to configure the db.config and default.yaml files with the name of your replica set and the IP addresses of each of the servers in the set.

To modify the primary server’s configuration files:

  1. In the directory …\Program Files\Sisense\PrismWeb\App_Data\Configurations backup the file db.config.
  2. In the original db.config file of the primary server, modify the value of the parameter connectionString to:
    "mongodb://AppUser:[email protected]:27018,XX.XX.XX.XX:27018,XX.XX.XX.XX:27018/admin?replicaset=<replicasetname>&amp;readPreference=primaryPreferred"
    replacing ‘@localhost:27018/admin’ with the IP addresses of each of your nodes in the replica set separated by a comma and replacing replicasetname with the name of your replica set. After you have added the IP addresses and your replica set name, your connection string should look like this:
    <add name="MongoProvider" connectionString="mongodb://AppUser:[email protected]:27018,XX.XX.XX.XX:2
    018,XX.XX.XX.XX:27018/admin?
    replicaset=<replicasetname>&amp;readPreference=primaryPreferred" mongoAppIV="ePtH5UDfff6YvSR0ihwkcQ==" mongoAppKey="dDRRM1p3SSS85N0JlQ3g4Mg=="SslPfxFilePath="" SslPfxFilePassword=""/></connectionStrings>

    For example:


    Note: The only values that should be changed are the IP addresses in the connectionString and replicatesetname.
  3. In the directory “…\Program Files\Sisense\PrismWeb\vnext\config”, back up the file default.yaml.
  4. In the original default.yaml file, uncomment the section replica_set replacing the value of the parameter name with the name of your replica set and XX.XX.XX.XX with the IP addresses of each of the nodes in your replica set:
    replica_set:
    name: <replicasetname>
    servers:
    - host: XX.XX.X.XX
    port: 27018
    - host: XX.XX.X.XX
    port: 27018
    - host: XX.XX.X.XX
    port: 27018

    For example:

  5. Depending on your Sisense installation,
    Full IIS: Open the IIS Manager and start the website SisenseWeb.
    IIS Express: Open Windows Services and start the service Sisense.WebExpress.

Connecting Additional Web Servers to Your Replica Set

To add additional nodes to support your primary web server, each additional server must be configured with the IP address of the primary and its AppUser credentials. The AppUser defines who can access the application database. For more information about AppUsers, see Accessing the Sisense Application Database.

This procedure describes how to configure additional nodes for high availability.

To configure your nodes:

  1. In the directory ..\Program Files\Sisense\PrismWeb\App_Data\Configurations back up the file db.config.
  2. In the original db.config file for each of your query nodes, replace the following line with the lines from the same file in the primary node.
    <add name="MongoProvider" connectionString="mongodb://AppUser:[email protected]:27018,XX.XX.XX.XX:27018,XX.XX.XX.XX:27018/admin?replicaset=<replicasetname>&amp;readPreference=primaryPreferred" mongoAppIV="ePtH5UDfff6YvSR0ihwkcQ==" mongoAppKey="dDRRM1p3SSS85N0JlQ3g4Mg==" SslPfxFilePath="" SslPfxFilePassword=""/></connectionStrings>
    Where XX.XX.XX.XX:27018 is the IP address and port of your primary node and <replicasetname> with your replica set name. All the content from the parameter connectionString should be included including mongoAppIV and mongoAppKey as this information is essential to enabling a connection between the primary and secondary nodes.
  3. Open the default.yaml file located in C:\Program Files\Sisense\PrismWeb\vnext\config\.
  4. Edit the file replacing the db_security, replica_set and db_users sections with your parameters from the default.yaml file stored on the primary node.
    db_security:
    mongoAppUser: AppUser
    mongoAppPassword: we6jBUsdrh0K6l+XpTmA==
    mongoAppKey: dDRsdm85N0JlQ3g4Mg==
    mongoAppIV: ePtH5ds6YvSR0ihwkcQ==
    replica_set:
    name: ‘replicaSet name’
    servers:
    – host: hostHame
    port: port
    – host: hostHame
    port: port
    – host: hostHame
    port: port
  5. Open the default.yaml file located in C:\Program Files\Sisense\PrismWeb\ECMNext\GraphQL\src\config. This file is used to support the web-based ElastiCube Manager.
  6. Edit the file replacing the same parameters modified in the default.yaml in Step 4. The naming used in this default.yaml file closely resembles that used in the default.yaml described in Step 4, but there are some differences. The table below maps out the relevant parameters that need to be changed and what sections they are located in within their file: 
    Step 4 default.yamlStep 6 default.yaml
    ParameterSectionParameterSection
    hostdb_serverhostmongo
    portdb_serverportmongo
    mongoAppUserdb_securityusermongo
    mongoAppPassworddb_securitypasswordmongo
    mongoAppKeydb_securitykeymongo
    mongoAppIVdb_securityIVmongo
    sslEnableddb_securitysslEnableddbSecurity
    certFilePathdb_securitycertFilePathdbSecurity
    pemKeyFilePathdb_securitypemKeyFilePathdbSecurity
    pemKeyFilePassworddb_securitypemKeyFilePassworddbSecurity
    caFilePathdb_securitycaFilePathdbSecurity
    namereplica_setnamereplicaSet
    servers
    host: hostName
    port: port
    replica_setservers
    host: hostName
    port: port
    replicaSet
  7. Depending on your Sisense installation:
    Full IIS: Open the IIS Manager and start the website SisenseWeb.
    IIS Express: Open Windows Services and start the service Sisense.WebExpress.
    Note: If you want to use the web-based ElastiCube Manager in a high availability configuration, the build node needs to be included in the replica set. For more information, see Supporting the Web-Based ElastiCube Manager in High Availability Environments.

Defining the Application Database’s Read Preferences

Read preferences specify where each member of a replica set should direct its read operations.

If you have implemented high availability with replica sets to support your Sisense implementation, and your application databases are geographically spread out, you may want to define different read preferences to different nodes within the set.

Defining your own read preferences is useful in cases such as preventing a secondary node which has fallen behind a primary node from giving old data.

You can define the following read preferences for the application database:

For additional information about read preferences, click here.

To modify the application database’s read preferences, you must update two files located in your Sisense server, the db.config file and the default.yaml file.

The db.config file is located in the following directory:

C:\Program Files\Sisense\PrismWeb\App_Data\Configurations

Open the db.config in a text editor and add the property readPreference=”yourPreferenceType” to the MongoProvider connection string.

<add name="MongoProvider" connectionString="mongodb://AppUser:mzfsb/[email protected]:27018,10.60.22.64:27018,10.60.22.74:27018/admin?replicaset=sisense;readPreference=primaryPreferred" mongoAppIV="ePtHfsdvvSR0ihwkcQ=="  mongoAppKey="ZzJBOFhfsdTW02aw==" SslPfxFilePath="" SslPfxFilePassword=""/></connectionStrings>

The default.yaml file is located in the following directory:

C:\Program Files\Sisense\PrismWeb\vnext\config

Open the default.yaml file in a text editor and add the property readPreference: ‘yourPreferenceType’ to the replica set object.

replica_set:
name: ‘replicaSet name’
readPreference: 'primaryPrefered'
servers:
– host: hostHame
port: port
– host: hostHame
port: port
– host: hostHame
port: port

Note: Remember, the comments '#' must be removed from each row of the replica_set object.